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
vote
1answer
235 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
464 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
33 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
67 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
34 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
51 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
37 views
2
votes
1answer
45 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
68 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
98 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
76 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
33 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
39 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
31 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
37k 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
65 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
115 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
33 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
349 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
75 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
19 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
194 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
32 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
33 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
420 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
28 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
74 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
96 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
316 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
179 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
57 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
119 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
739 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
63 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
64 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
126 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 ...
6
votes
3answers
400 views

MySQL single table static and dynamic pivot

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 |...