Stack Overflow
Stack Overflow

Questions tagged [pivot]

The PIVOT syntax converts row data into columnar data, and vice versa for the UNPIVOT syntax. The syntax is non-standard, and not all databases support the PIVOT syntax, but the functionality can be implemented, often using decision logic (CASE expressions, etc) and aggregate functions.

The PIVOT syntax converts row data into columnar data, and vice versa for the UNPIVOT syntax. The syntax is non-standard, and not all databases support the PIVOT syntax, but the functionality can be implemented, often using decision logic (CASE expressions, etc) and aggregate functions.

The PIVOT operation may also be referred to as cross-tabulation or .

Similar functionality also exists in data processing tools/packages such as (via pivot method), (via pivot_wider function), and (via unstack function).

Databases that support PIVOT/UNPIVOT syntax:

  • Oracle 11g+
  • SQL Server 2005+

While MySQL does not have a PIVOT function, this can be replicated using an aggregate function and either a CASE statement or IF()

PostgreSQL (true in 9.3 and below, at least) do not offer PIVOT, but do supply the crosstab function from the bundled tablefunc extension. This function may be used, albeit awkwardly, to achieve the same effect.

Reference:

11340 questions
37
votes
3 answers

Pivot rows to columns without aggregate

Trying to figure how to write a dynamic pivot sql statement. Where TEST_NAME could have up to 12 different values (thus having 12 columns). Some of the VAL will be Int, Decimal, or Varchar data types. Most of the examples I have seen have some…
jlimited
  • 685
  • 2
  • 11
  • 20
36
votes
2 answers

One-to-Many SQL SELECT into single row

I have data in two tables. The first table has a Primary Key called PKID PKID DATA 0 myData0 1 myData1 2 myData2 The second table has the PKID column from table 1 as a foreign key PKID_FROM_TABLE_1 U_DATA 0 unique0 …
Grizz
  • 603
  • 2
  • 7
  • 11
35
votes
2 answers

SQL Server pivot vs. multiple join

What is more efficient to use in SQL Server 2005: PIVOT or MULTIPLE JOIN? For example, I got this query using two joins: SELECT p.name, pc1.code as code1, pc2.code as code2 FROM product p INNER JOIN product_code pc1 ON…
Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116
35
votes
6 answers

Dynamic alternative to pivot with CASE and GROUP BY

I have a table that looks like this: id feh bar 1 10 A 2 20 A 3 3 B 4 4 B 5 5 C 6 6 D 7 7 D 8 8 D And I want it to look like this: bar val1 val2 val3 A 10 20…
flipflop99
  • 583
  • 1
  • 5
  • 9
33
votes
1 answer

Dynamically create columns sql

I have a table of Customers Customer ID Name 1 John 2 Lewis 3 Mary I have another table CustomerRewards TypeID Description 1 …
CR41G14
  • 5,464
  • 5
  • 43
  • 64
32
votes
2 answers

Example of an Oracle PIVOT clause with subquery

Oracle's definition of the PIVOT clause specifies that there is a possibility to define a subquery in the IN clause. A fictional example of what I would imagine this to be is this ... PIVOT (AVG(salary) FOR (company) IN (SELECT DISTINCT company FROM…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
32
votes
2 answers

SQL - How to transpose?

I have something similar to the following table: ================================================ | Id | UserId | FieldName | FieldValue | =====+========+===============+================| | 1 | 100 | Username | John Doe …
StackOverflowNewbie
  • 39,403
  • 111
  • 277
  • 441
31
votes
4 answers

Pivoting rows into columns dynamically in Oracle

I have the following Oracle 10g table called _kv: select * from _kv ID K V ---- ----- ----- 1 name Bob 1 age 30 1 gender male 2 name Susan 2 status married I'd like to turn my keys…
ojosilva
  • 1,984
  • 2
  • 15
  • 21
31
votes
4 answers

How to pivot on multiple columns in Spark SQL?

I need to pivot more than one column in a PySpark dataframe. Sample dataframe: from pyspark.sql import functions as F d =…
Suresh
  • 5,678
  • 2
  • 24
  • 40
30
votes
2 answers

Unpivot in Spark SQL / PySpark

I have a problem statement at hand wherein I want to unpivot table in Spark SQL / PySpark. I have gone through the documentation and I could see there is support only for pivot, but no support for un-pivot so far. Is there a way I can achieve…
Manish Mehra
  • 1,381
  • 1
  • 16
  • 24
30
votes
3 answers

Mysql Convert Column to row (Pivot table )

I have a table like this id month col1 col2 col3 col4 101 Jan A B NULL B 102 feb C A G E And then I want to create report like this desc jan feb col1 A C col2 B A col3 0 G Col4 B E Can anyone help with this?
user1914516
  • 339
  • 1
  • 3
  • 3
28
votes
7 answers

How to pivot a MySQL entity-attribute-value schema

I need to design tables which stores all the metadata of files (i.e., file name, author, title, date created), and custom metadata (which has been added to files by users, e.g. CustUseBy, CustSendBy). The number of custom metadata fields cannot be…
Ashok
28
votes
3 answers

Is it possible to have multiple pivots using the same pivot column using SQL Server

I am facing the following challenge. I need to rotate table data twice over the same column. Here's a screenshot of the data. I want to have one row for each Item ID containing both the purchasing value and the selling value for each year. I tried…
Rob Vermeulen
  • 1,910
  • 1
  • 15
  • 22
27
votes
1 answer

SQL transpose full table

I need to do the following transpose in MS SQL from: Day A B --------- Mon 1 2 Tue 3 4 Wed 5 6 Thu 7 8 Fri 9 0 To the following: Value Mon Tue Wed Thu Fri -------------------------- A 1 3 5 7 9 B 2 4 6 8 0 I…
Selrac
  • 2,203
  • 9
  • 41
  • 84
27
votes
4 answers

Sql PIVOT and string concatenation aggregate

I would like to use a pivot SQL query to construct a result table where the concatenate text as a result within the DATA section of the pivot table. i.e. i have the following result from using a simple…
Mortalus
  • 10,574
  • 11
  • 67
  • 117
1 2
3
…
99 100