Amazon Redshift Pivot using Concatenation

Question

I have a table which looks like

|------|-------|---------|
|foo_id|country|foo_value|
|------|-------|---------|
|  1   |   US  |   abc   |
|------|-------|---------|
|  1   |   IN  |   def   |
|------|-------|---------|
|  1   |   DE  |   abc   |
|------|-------|---------|
|  2   |   US  |   xyz   |
|------|-------|---------|
|  2   |   IN  |   klm   |
|------|-------|---------|

Here the combination of foo_id and country is unique. So there will be only 1 row for 1 and US.

I want to pivot it as

|------|--------|---------|---------|
|foo_id|   US   |   IN    |    DE   | 
|------|--------|---------|---------|
|  1   |   abc  |   def   |    abc  |
|------|--------|---------|---------|
|  2   |   xyz  |   klm   |         |
|------|--------|---------|---------|

I googled for this question and I found the answer for SQL Server and it uses "for XML".

Sql PIVOT and string concatenation aggregate

This answer is using "FOR XML" but I'm not sure why I should use "FOR XML" when I'm not dealing with XML at all. Is there a better answer which is pure ANSI SQL or perhaps better suited for redshift?

GMB · Accepted Answer · 2019-09-01T23:43:18.633

2

You can use conditional aggregation:

SELECT 
    foo_id,
    MAX(CASE WHEN country = 'US' THEN foo_value END) AS US,
    MAX(CASE WHEN country = 'IN' THEN foo_value END) AS "IN",
    MAX(CASE WHEN country = 'DE' THEN foo_value END) AS DE
FROM mytable
GROUP BY foo_id

edited Sep 01 '19 at 23:43

answered Sep 01 '19 at 23:24

GMB

216,147
25
84
135

Thanks. do you know how to escape IN? it seems its also a clause in SQL. I tried `'IN'` but that doesn't work – Knows Not Much Sep 01 '19 at 23:40
@KnowsNotMuch: my bad, should be `"IN"`. – GMB Sep 01 '19 at 23:44
Not sure what I'm doing wrong but when I try to do a group by I get error `Invalid operation: failed to find conversion function from "unknown" to character varying` – Knows Not Much Sep 01 '19 at 23:56

Amazon Redshift Pivot using Concatenation

1 Answers1