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.

learn more… | top users | synonyms (2)

-1
votes
2answers
74 views

How to PIVOT/UNPIVOT if I don't know how many columns will it have?

I have this simple query: I want to list something like this: Unidade1 \ PLaca1 \ Placa2 \ Placa3 Unidade2 \ Placa1 \ Placa2 But I don't know how many placas a unidade will have. I was ...
1
vote
1answer
269 views

MySQL query to create a Matrix view using pivot

I have a table with the following fields: p1 p2 s1 s2 where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2 The number of players can vary from 3 - 10. The table have the results between ...
2
votes
1answer
470 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
0
votes
0answers
34 views

Unable to fetch data based on latest date and time

I have a table which stores application details. Applications are nothing but a set of data showing scores. Each application has 6 parts namely 1,2,3,4,5, and 6. So, in the database table parts can ...
2
votes
1answer
68 views

How to merge multiple rows into one row with SQL? [duplicate]

Originally, I have one table, storing UserID and the corresponding value based on Year, Month, as shown below: Now, I want to 'merge' the rows according to the Year and Month value and to store ...
1
vote
1answer
46 views

Consolidate multiple rows into single row

In PostgreSQL 9.5, I have a table named reports: CREATE TABLE public.reports ( id BIGSERIAL PRIMARY KEY, id_station character(11) NOT NULL, date date NOT NULL, element character(4) NOT NULL, ...
0
votes
1answer
60 views

Create pivot table from SQL query results

I need to convert the results from my SCCM 2012 query about user device affinity to show multiple columns per computer name. Example of query results :- Desired results format :- Is it possible ...
-3
votes
1answer
37 views

How link data - delimiter

How can I make script take mydata from mytable instead of providing values like in an example below? SO instead of set values to Code and Location link these to mycolumns in mytables? with data as ( ...
0
votes
1answer
42 views
2
votes
1answer
51 views

Dynamic Parameters In Pivot

I know the premise of a PIVOT() this syntax produces it perfectly for me: Select * FROM ( Select field1 ,annual ,dv As Amt FROM Testing ) x pivot ( SUM(Amt) ,for annual IN ([2014],[...
17
votes
3answers
6k views

Dynamically Define a Range in a Dimension

I have an issue that I face every time I decide to build a cube, and I haven't found a way to overcome it yet. The issue is how to allow the user to define a range of things automatically without ...
4
votes
1answer
82 views

Is it possible to create a dynamic join?

I'm using PostgreSQL if it matters. Assuming these two tables: id | run ----+------- 1 | run_1 2 | run_2 3 | run_3 and sample | elapsed | run -------+---------+------- samp1 | 1:09 |...
0
votes
3answers
120 views

How can I pivot a view into a join so I get multiple query rows as columns in a single row? [duplicate]

I have a view into which I am pulling properties of vehicles, such as tire size, engine capacity, color, number of doors, etc. This data is stored in multiple tables but I have simplified the data ...
2
votes
1answer
21 views

Convert multiple rows into column base on another column value [duplicate]

I have search through the answer but could not get what i'd like to have. my table is as below. No | Name | Relation 1 | A | P 1 | B | GO 1 ...
0
votes
0answers
20 views

Issue in pivot operator

Find the employee is in which shift and shifttype If he is shift 1 shifttype 1then the record comes from employee table if he is shift 2 shifttype 4 then the record comes from shiftscheduler table ...
0
votes
1answer
27 views

Append Records to a fixed number of columns using dynamic data [duplicate]

I have a table with 22,000 records formatted like so: Input [ID],[Cost Center],[Function] 1,01A1001710,GF000001 2,01A1001710,GF000802 3,02R1012000,GF000001 4,02R1012000,GF000059 5,02R1012000,...
1
vote
2answers
129 views

Join two tables using pivot combining two columns in one table

I have two tables in this format: Job_Skill ID_Skill Min_Job Idea_Job Max_Job ===================================== 1 0 0 1 2 0 1 1 7 1 ...
0
votes
0answers
8 views

T-SQL to MySQL conversion for PIVOT [duplicate]

I made a garbage table with garbage data and a PIVOT on it in T-SQL format and I would like to convert it into MySQL but I can't figure out how to get it to work. Table and data: CREATE TABLE ...
1
vote
1answer
35 views

Pivot three fields into one category

I have data in this format Skill Min Ideal Max ========================== C++ 0 1 1 I want to display it like this: Criteria C++ ================= Min ...
0
votes
2answers
43 views

How to separate values from one column into two columns in SQL Server [closed]

I have this dataset as input: Name Contact No ------- ---------- Deepika 75865558 Deepika 207586558 Rnveer 746655558 Rnveer 946655558 vaibhav 2356841 vaibhav 6655558 I want to get ...
0
votes
0answers
32 views

Convert table values into column header without PIVOT [number of column values is dynamic] - Oracle 10g [duplicate]

I have a table in the following format ID NUM P_VAL Q_VAL ---------------------------------------- 445120000 00 101 25 445120000 10 103 25 445120002 00 ...
14
votes
3answers
39k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
2
votes
1answer
30 views

How can I improve my query, using joins & improving performance?

I have a really smelly query which I'm not sure how to simplify, this is quite an intensive query. I have tried various union and inner joins in an attempt to simplify it and my biggest problem is ...
3
votes
3answers
73 views

How to Pivot in PostgreSQL

Having the following data in a table: ID Category Value 1234 Cat01 V001 1234 Cat02 V002 1234 Cat03 V003 1234 Cat03 V004 1234 Cat03 V005 I want to have the ...
1
vote
2answers
119 views

Converting rows to columns

I have data like following: created_at | status ---------------------+------------- 2016-04-05 1:27:15 | info 2016-04-05 3:27:15 | info 2016-04-05 5:27:15 | warn 2016-04-05 ...
0
votes
1answer
35 views

SQL Server Pivot Multiple Column and Value [duplicate]

I have a sample table attendance look like this : PERIOD EMPL_NMBR EMPL_NAME DRPT DAYX [IN ] [OUT] 201603 00001 John QA 01 08:00 17:00 ...
6
votes
4answers
363 views

Convert row into concatenated string with column names included

I have a table that is several hundred columns wide. Is there a way to convert each row into a single concatenated string with the column title included without having to list each single column in ...
2
votes
1answer
77 views

Problem using pivot to display data

I currently have a table called sample, the structure of my attendance table is: create table sample ( Student_ID VARCHAR2 (10), Late_Attendance number (10) Default 3, Attendance_Date Date, ...
0
votes
0answers
20 views

MySQL IF Statement works for PIVOT scenario… Until I GROUP BY

I've been trying for days to figure out how to pivot data to give me this appearance: I've written the following code to generate the data requested and it works. But When I use GROUP BY to ...
3
votes
3answers
196 views

Two rows into two columns

I have this table: Name Value Sequence ------------------------------- test A1 1 test A2 3 test2 A20 5 ...
0
votes
0answers
10 views

How to create Pivot table with dynamic columns in MySQL [duplicate]

I'm using the following table for storing dynamic form data data: Using the following query to select the records from my table SELECT response_no, GROUP_CONCAT(if(...
0
votes
0answers
39 views

Table based function with variable columns

I have a situation where I need to create a table based function but don't know the number of columns that need to be returned at the point of defining it. Essentially, I'm using a pivot table to ...
1
vote
0answers
34 views

Pivot table with date_shutdown and factory_name and type_shutdown and time_shutdown

I need to make a pivot table that includes a factory name column along with a column for every type of shutdown; in the intersection the period of the shutdown. Factory names and shutdown types are ...
1
vote
0answers
35 views

how to edit kvp data as crosstab/pivot

I am keeping data in key/value pairs. I know how to convert that data into crosstab form using queries like this: <!-- language: lang-sql --> SELECT master.tag, MAX(CASE WHEN data.key = ...
5
votes
1answer
422 views

Help with Oracle Pipelined function

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works): SELECT oks_c.contract_number, oks_l....
0
votes
1answer
32 views

Pivoting Dynamic Rows into Columns in MySQL [duplicate]

I have two tables Student_Attendance and Students. I am using this query: SELECT sa.Student_Id, sa.Attendance_Date, CASE WHEN sa.Attendance_Status as Status FROM student_attendance sa ...
1
vote
1answer
87 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 ...
4
votes
1answer
109 views

How to get multiple row data into a row with multiple columns

I have a MySQL table like this: User_Id course_name course_location course_id 1 course name 1 location 1 1 1 course name 2 location 2 2 1 course name 3 ...
6
votes
1answer
357 views

Insert data from multiple rows into multiple columns

I cannot find, after much searching, how this would be done. My web searches get Pivots and Concats and Cases and Subqueries etc, none of it is quite solving the issue for me. The multiple-rows-to-...
3
votes
1answer
204 views

RDS crosstab: ERROR: permission denied for language c

We're currently using PostgreSQL 9.2 in our production database, we want to migrate to a new RDS instance, using PostgreSQL 9.4. However, we need to have permission to create a function, but only ...
1
vote
1answer
60 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 ...
1
vote
2answers
121 views

Transforming multiple rows into a single row

In my company, I need to join table A to table B: Table A is the sources table Table B is the empty table I want to know that how can I do it and the result like table B? Source table A: ID ...
2
votes
1answer
41 views

How can I have flexible PIVOT column names?

Following the example in https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/, I have a PIVOT that works but is not very flexible. It is basically SELECT * FROM (...
0
votes
0answers
32 views

How can I display values as columns dynamically using postgresql

I have a data table containing finance data about some filings like: filing_id | custom_label | value ------------------------------------ 987345 | cash | 567 987345 | ...
1
vote
2answers
50 views

How to turn this query into a Pivot?

If theres something I can't learn, is PIVOT. People are trying to teach me this, I'm reading blogs about this but I just can't understand the logic. How can I turn this query , into a pivot? The ...
0
votes
1answer
807 views

How to convert rows into columns and query on them in Mysql?

I have three tables Patients, which contains the name of my patients, Controls which represents the UI controls which could be drawn for each illness , and ControlsValues table which contains the ...
0
votes
2answers
68 views

How to transpose rows to column in datatabse [duplicate]

I have one table which contain 3 column,Col_ID, Col_Group, Col_Price for this my column is like below Col_ID Col_Group Col_Price 1 AA 0 1 BB 2000 1 ...
0
votes
3answers
65 views

Using start and end event logs to create a table/view containing spans between the times of each log

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | -...
0
votes
2answers
128 views

Rows into Column

I want to make the above view vConsolPrchOrd like below: Database: PostgreSQL 9.2 I want to create a Crystal Report with this view (I asked about this on Stack Overflow).
0
votes
0answers
28 views

How to split one column data into multiple columns [duplicate]

I have a table called Items. First column(Items) have n number of Split Items. select * from items ITEMS SPLIT ITEMS 1111 2525 1111 4587 1111 9874 1111 8787 ... Instead of giving ...