CODEWITHSUNDEEP
amazon-redshift
Akshay Singh
Akshay Singh

Reputation: 67

Make row data into column headers while grouping

I'm trying to group up on a multiple rows of similar data and convert differentiated row data into columns on Amazon Redshift. Easier to explain with an example ->

Starting Table

+-------------------------------------------+
|**Col1** | **Col2** | **Col3** | **Col 4** |
|    x    |    y     |    A     |    123    |
|    x    |    y     |    B     |    456    |
+-------------------------------------------+

End result desired

+-------------------------------------------+
|**Col1** | **Col2** |  **A**   |   **B**   |
|    x    |    y     |    123   |    456    |
+-------------------------------------------+

Essentially grouping by Column 1 and 2, and the entries in Column 3 become the new column headers and the entries in Column 4 become the entries for the new columns.

Any help super appreciated!

Upvotes: 1

Views: 605

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 270224

There is no native functionality, but you could do something like:

SELECT
  COL1,
  COL2,
  MAX(CASE WHEN COL3='A' THEN COL4 END) AS A,
  MAX(CASE WHEN COL3='B' THEN COL4 END) AS B
FROM table
GROUP BY COL1, COL2

You effectively need to hard-code the column names. It's not possible to automatically define columns based on the data.

This is standard SQL - nothing specific to Amazon Redshift.

Upvotes: 1

Related Questions