Skip to main content

Questions tagged [pivot]

A pivot (or crosstab) converts row data into columnar data, and vice versa for an unpivot. Not all databases support explicit PIVOT syntax but the functionality can be implemented, often using decision logic (CASE statements, etc), aggregate functions, or an extension.

Filter by
Sorted by
Tagged with
1 vote
1 answer
642 views

SQL pivot/transpose/row-column non-proprietary

Maybe this question has been asked already (it should be quite simple). I've Googled loads of phrases (PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is TSQL this, TSQL that and or Oracle ...
user avatar
1 vote
1 answer
4k views

column alias in dynamic pivot [duplicate]

I need help with a dynamic pivot query. I would like to modify the column name (alias) but I can't get it done in the pivot. Please help. The query itself works perfectly but the username contains the ...
matt Forchette's user avatar
1 vote
1 answer
1k views

SQL Server pivot filter underlying tables by aliases specified in view

I have table structure as follows: CREATE TABLE [dbo].[Test] ( [ID] [int] IDENTITY(1, 1) NOT NULL , [ProductID] [int] NOT NULL , [CountryID] [int] NOT NULL , CONSTRAINT [PK_Test] PRIMARY ...
Evaldas Buinauskas's user avatar
1 vote
2 answers
9k views

Select Multiple Values from Same Column; one sql statement and split into separate columns

This post derives from following - Select Multiple Values from Same Column; one sql statment Based on this query - SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) FROM t WHERE meta_key ...
user2680821's user avatar
1 vote
2 answers
104 views

Pivot column query

I'm trying to figure out how to create a From and To column based on ID.LocationID column. I'm hoping with a suggestion this can be done (I can figure it out) without having to create sample tables ...
jhowe's user avatar
  • 281
1 vote
1 answer
622 views

Pivot a table to repeat the first column for each following column

i'm not sure how to explain so I'll try and write out an example. I'm currently using an access database with a table in the following format 1 A B C D 2 A B C D I'm trying to perform a query that ...
Dirk R.'s user avatar
  • 45
1 vote
1 answer
32 views

Grouping results belonging to the same product number into the same record

I have a very long query that I have shortened and anonymized so the problem becomes clearer. Here is an approximation of my query: SELECT subquery1.productNumber, subquery1.anotherinfo, ...
Guest's user avatar
  • 11
1 vote
1 answer
1k views

Transpose 1 row into columns with count/sum

I have a a single row with age buckets as column names. I want them all in one column with their count or sum whatever I end up doing. I have quite a few buckets but for the sake of this example I've ...
NasahT's user avatar
  • 25
1 vote
1 answer
534 views

Flatten a JSONB file - What function/query

In a bit of a pickle at work - I have a Jsonb which I need to flatten a file that has similar structure: { "Manufacturers":[ { "manufacturer":"Tesla", ...
NasahT's user avatar
  • 25
1 vote
1 answer
282 views

Fetch Different Values in different column for a specific id

I have a data as shown below: I want the output as shown below in Postgress or in Excel.
RISHAB GUPTA's user avatar
1 vote
1 answer
4k views

Group by latest date with conditions

I need your help! Let's say I have this table: Instance Date MetricID Value Marc 09/14/21 1 5 Marc 09/14/21 2 2 Marc 09/14/21 3 1 John 09/14/21 1 10 John 09/14/21 2 1 John 09/14/21 3 1 Marc 09/...
no name sry's user avatar
1 vote
2 answers
55 views

Pivoting table in MySQL

I was wondering if it is possible to change the output of this: User Database Select Insert Update Delete Create References Alter Drop ------------- ------------------...
the_ruffler's user avatar
1 vote
3 answers
119 views

Filter table by multiple pivot values

I have the following tables: fabrics | id| title | code| |-- |---------| ----| | 1 | Fabric 1| FBR1| | 2 | Fabric 2| FBR2| | 3 | Fabric 3| FBR3| | 4 | Fabric 4| FBR4| attributes | id| title | ...
Shile's user avatar
  • 121
1 vote
1 answer
151 views

MySQL DB design for very long growing row size

My data has the property to grow in the row direction and the column direction at the same time. The size will grow in both directions without limit while I do more research. The number of columns ...
hbadger19042's user avatar
1 vote
1 answer
130 views

Create A Pivot With No SUM() in MySQL

I've seen a lot of examples using MySQL to create a PIVOT but they all use SUM() - using this DDL how can I create a PIVOT in MySQL Create Table `data` ( callDate text ,callerName text ,...
jamesMandatory's user avatar
1 vote
1 answer
36 views

Transposing a row data in multiple rows based on multiple columns

I have data in the database in the below format And I want output data in this format BudgetCategoryId | CostCenterDepartmentId | CostCenter | AccountNo | Amount | Timestamp 714 | ...
Mayur Agarwal's user avatar
1 vote
1 answer
26 views

Aggregating Attributes

I have a product review table in an oracle 12.1 EE database that has different attributes rated on a scale of 1 through 5. How do I query this table to count up all of the reviews and group by the ...
Nick S's user avatar
  • 631
1 vote
1 answer
720 views

Using PIVOT to convert multiple rows and columns

So my current query output is as follows: I would like to consolidate all of these rows to just one long row like so: MyID Customer relc_rate relc_consumption relc_daysofservice ...
MindXpert's user avatar
1 vote
1 answer
56 views

SQL sort by sum of two different where clauses in a join

table1 ID | Name 1 ID1 2 ID2 3 ID3 table2 ID | table1ID | Values 1 1 3 2 2 3 3 3 2 4 1 3 5 2 1 6 3 2 7 1 ...
Thomas Saunders's user avatar
1 vote
1 answer
10k views

Combining multiple rows into a single row with multiple columns

I am trying to do a manual query for WordPress postmeta table which would combine two types of postmeta into one with multiple columns by post_id. This is the structure of table: -------------------...
Andrius Solopovas's user avatar
1 vote
2 answers
3k views

SQLServer - Multiple PIVOT on same column

I have a table data like the below with Type Regular/Irregular from January to December. I need a query to do this data like the below table: January with below divided into two-jRegular & ...
H A S I B's user avatar
1 vote
1 answer
71 views

Pivot information - sql table

I was wondering if anyone could help me with pivoting data in sql? I am a newbies and need some help I want to pivot data based on "LastLogOn" column. Is it possible to do in SQL? From: To:
Bob's user avatar
  • 11
1 vote
1 answer
346 views

How to pivot a column showing its content as new columns

I'm working with MySQL 5.6 and I'm having headaches with this issue. I've been looking in several groups the result, but I think this isn't very common. I have a table like the following one: direc ...
Victor Lopez's user avatar
1 vote
1 answer
2k views

Transpose Rows to column

Can anyone Please help me to transpose below result +------+------------+-------+ | SVR | dates | count | +------+------------+-------+ | SRV1 | 03/25/2018 | 1 | | SRV1 | 04/06/2018 | 59 ...
dba's user avatar
  • 11
1 vote
1 answer
3k views

How do I add extra columns to crosstab()?

I am using PostgreSQL 9.6 and have following schema CREATE TABLE activitylog ( sessionid text NOT NULL, studentid integer NOT NULL, activity text NOT NULL, created timestamp without ...
Andrew-Dufresne's user avatar
1 vote
1 answer
322 views

Pivot add-on in cross-tab table Postgresql

Given the schema here I wonder how I can use Pivot add-on to calculate a cross-tab table in the below format please? Africa America Asia Australia/Oceania Europe Total #cities #cities ...
Tak's user avatar
  • 143
1 vote
2 answers
1k views

Querying data from a pivoted MySQL table takes a long time to execute

TL;DR Querying data from a two table database I've setup a long time ago takes a really long time to execute. Back when I was a student, quite some time ago, I developed a simple survey tool for a ...
Kaspar's user avatar
  • 11
1 vote
1 answer
1k views

Referring to function parameters inside a Postgres function

I have a function like this in Postgres 9.5: CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS SETOF some_datum AS $func$ BEGIN RETURN ...
Randomize's user avatar
  • 1,183
1 vote
1 answer
2k views

Join Multiple Derived Tables, Correctly in MySQL?

I'm trying to achieve a full outer join in MySQL EDIT : As per comments: Data is Need data with for question_id in(44,45,46,47,48,49) Desired Output is : Query generated using golang: select @s:=...
Garvit Jain's user avatar
1 vote
1 answer
2k views

Issue with SUM OVER PARTITION BY in conjunction with GROUP BY

I am getting a typical group by error that one would see when a field is not in a group by or an aggregate in the select. Column 'v.EDTAmount' is invalid in the select list because it is not ...
Jeff.Clark's user avatar
1 vote
1 answer
541 views

Subtracting sets within a grouped column, should I be pivoting?

I'm not the best at explaining, but I have a table that has the format CREATE TABLE foo AS SELECT type,date::date,tp,price FROM ( VALUES ( 'A', '2010-10-01', 1, 0.05 ), ( 'A', '2010-10-01'...
Billy Fung's user avatar
1 vote
1 answer
105 views

Where and how to add selective totals in SSRS

I need to get some totals output in SSRS that I am having difficulty putting together. I am not sure if I should do the totals for the below query within the query, or in SSRS. The output is going to ...
Jeff.Clark's user avatar
1 vote
1 answer
3k views

Like UNPIVOT in SQL!

How to change the way that cols orders it self? If we have a table like this a b c 1 xx xy 2 xy yx 3 yy xx Since both of b and c are same type I ...
Suhayb's user avatar
  • 123
1 vote
1 answer
8k views

Mysql table column to row

i am in difficulties in doing following table (mysql). i have stdent-id,questions,answers and questions-id of a table. below given, is there any way to output table something below output table? My ...
Shimul Chowdhury's user avatar
1 vote
1 answer
919 views

Is there a way to construct a PIVOT's IN clause using a SELECT?

I want to build a PIVOT table, but I may have to use dynamic SQL unless the IN clause can be constructed in regular SQL. I want to do something like this: SELECT * FROM ( SELECT x, y, z... ) ...
Jez's user avatar
  • 695
1 vote
1 answer
50 views

Monthly grouped data

OK! I've a table that looks like this: SQLFiddle And I want to get some handlers to provide to my renderer php page, so that the final result be like this: +----------------+--------------------...
Luís Alves's user avatar
1 vote
1 answer
4k views

Case Statement With Group By

I think it is my group by in this syntax that is skewing my desired result set. I ONLY want to see the data I am requesting, however my query returns all the data like this: salesman Q1 2015 Q1 ...
BellHopByDayAmetuerCoderByNigh's user avatar
1 vote
1 answer
575 views

Nested, inline pivot table (oracle)

I'm trying to go from a table like this: ID | NAME | METADATA ------------------------------- 1 | test | '{"a":"x","b":"y"}' where the metadata column is a JSON string. What I'd like to end up with ...
Justin Lewis's user avatar
1 vote
1 answer
625 views

Concatenate columns uniquely on the same table in MS ACCESS

The original table: ======================================================================================= | A | B | C | D | E | F | G | ====...
user71111's user avatar
1 vote
1 answer
523 views

Dynamic SQL in a pivot table

I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant ...
user2921015's user avatar
1 vote
1 answer
3k views

MySQL Pivot Table Dynamic Rows

I have a table like the one below: +-------+-----------+-----------+-----+--------+-------------+ | grade | firstname | lastname | age | gender | student_num | +-------+-----------+-----------+-----+...
manglide's user avatar
1 vote
1 answer
936 views

SQL query to return a columns with given range

I have a table tblItems with records having the same UniqueID and Description but with different dates and amount. Now I want to show the records by grouping them together using the UniqueIDs and then ...
Rishi Samadhiya's user avatar
1 vote
1 answer
104 views

How to improve query to include 2 critical pieces of employee data

I’m going to preface by saying that I’m fairly green with SQL. My code below works, however I am missing two vital pieces of my employee population: (1) those employees who have been invoiced but ...
Corey's user avatar
  • 35
1 vote
3 answers
1k views

Add fields to view from select (transpose fields)

I've got a table with following structure: CREATE TABLE test ( id integer, field character varying, value character varying ); field value ------ ----- field1 value1 field2 ...
Egidi's user avatar
  • 393
1 vote
1 answer
222 views

MySql pivot table for scatter chart [closed]

I would like to transform the following sql (e.g. select Name,X,Y from mytable) result: Name | X | Y bla1 | 0.1 | 0.2 bla2 | 0.3 | 0.4 Into the following form that is displayed correctly in an ...
isthisthat's user avatar
1 vote
1 answer
2k views

Cross tab query with concat field

I want to create a cross tab query for my table. When I use CONCAT I am unable to get output successfully. Can anyone help me with my query? SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT ...
maddy's user avatar
  • 49
1 vote
1 answer
1k views

How can I count different types in a column in sql? [duplicate]

Data: QID Type DOB ------------- 1 1 01/01/1980 1 2 03/01/1981 1 2 01/02/1991 1 1 01/01/1980 1 2 03/01/1981 ...
user39186's user avatar
1 vote
0 answers
14 views

PostgreSQL crosstab query not using view owner permission when queried from other users

I'd like some help with regards to creating a view in PostgreSQL using the crosstab function. I've been trying to create a view as follows: create or replace view csv_data as with answers as( select * ...
lsimonetii's user avatar
1 vote
0 answers
11 views

Power Pivot/Data model issue

Good day, I am facing two blocking points where I would need your insights please. I have downloaded all my sheets in power query and made the necessary transformations, including creating a common ...
Maissa Oueslati's user avatar
1 vote
1 answer
197 views

Oracle multiple PIVOT clauses

I have data with 2 calculated bucket columns I would like to summarize with a PIVOT query. This is an example set of pre-generated data. My bucket columns here are sec and pct. select '1' id, 'a' ...
Aaron's user avatar
  • 113

1
3 4
5
6 7
10