CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshifttransform
Ajinkya
Ajinkya

Reputation: 11

In redshift SQL, How to create a new column for each unique value in one column?

I have a table that looks like:

Key Value
1 A
1 B
2 A
2 B
2 C
2 D

I wish to convert it into:

Key Value1 Value2 Value3 Value4
1 A B - -
2 A B C D

Upvotes: 1

Views: 808

Answers (2)

Bill Weiner
Bill Weiner

Reputation: 11032

Another way to approach this is to assign the column by some ordering (alphabetical). Here's what this can look like for your data expanded (key = 3 added and expanded to 20 possible columns).

CREATE TABLE t1 
AS
SELECT 1 AS KEY,'A' AS Value UNION SELECT 1, 'B' 
    UNION SELECT 2, 'A' UNION SELECT 2, 'B' UNION SELECT 2, 'C' UNION SELECT 2, 'D' 
    UNION SELECT 3, 'X' UNION SELECT 3, 'Y' UNION SELECT 3, 'Z' 
;

select key,
    max(decode(rn, 1, value, '-')) as v1, 
    max(decode(rn, 2, value, '-')) as v2,
    max(decode(rn, 3, value, '-')) as v3,
    max(decode(rn, 4, value, '-')) as v4,
    max(decode(rn, 5, value, '-')) as v5,
    max(decode(rn, 6, value, '-')) as v6,
    max(decode(rn, 7, value, '-')) as v7,
    max(decode(rn, 8, value, '-')) as v8, 
    max(decode(rn, 9, value, '-')) as v9,
    max(decode(rn, 10, value, '-')) as v10,
    max(decode(rn, 11, value, '-')) as v11,
    max(decode(rn, 12, value, '-')) as v12,
    max(decode(rn, 13, value, '-')) as v13,
    max(decode(rn, 14, value, '-')) as v14,
    max(decode(rn, 15, value, '-')) as v15, 
    max(decode(rn, 16, value, '-')) as v16,
    max(decode(rn, 17, value, '-')) as v17,
    max(decode(rn, 18, value, '-')) as v18,
    max(decode(rn, 19, value, '-')) as v19,
    max(decode(rn, 20, value, '-')) as v20
from (
    select key, value, row_number() over (partition by key order by value) as rn
    from t1 )
group by key
order by key;

Note that I have used the DECODE() statement instead of CASE due to the better readability. You mentioned Redshift so this will work there but you may need to revert back to CASE for other databases.

The results in this approach look like:

key v1  v2  v3  v4  v5  v6  v7  v8  v9  v10 v11 v12 v13 v14 v15 v16 v17 v18 v19 v20
1   A   B   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
2   A   B   C   D   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
3   X   Y   Z   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -

Upvotes: 0

Rui Costa
Rui Costa

Reputation: 437

If you know how many Values you have, you can do it in a groupby:

SELECT Key,
MAX(CASE WHEN Value = 'A' THEN Value ELSE '-' END) AS Value1,
MAX(CASE WHEN Value = 'B' THEN Value ELSE '-' END) AS Value2,
MAX(CASE WHEN Value = 'C' THEN Value ELSE '-' END) AS Value3,
MAX(CASE WHEN Value = 'D' THEN Value ELSE '-' END) AS Value4
FROM
TABLE_NAME
GROUP BY Key

If the number of Values is unknown you cannot do with a generic code. This is due to columns not being Unlimited. https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_usage.html

Upvotes: 1

Related Questions