CODEWITHSUNDEEP
sqlpostgresqlapache-sparkapache-spark-sqlpivot
hotshot_02
hotshot_02

Reputation: 103

Unpivot postgres table having multiple columns

I want to pivot a table having some columns like below.

ID week1 week2 week3 week4 week5 week6 week7
1 8 9 10 11 12 13 14
2 15 16 17 18 19 20 21
3 22 23 24 25 26 27 28

The desired output is -

ID week_number week_value
1 1 8
1 2 9
1 3 10
1 4 11
1 5 12
1 6 13
1 7 14
2 1 15
2 2 16
2 3 17
2 4 18
2 5 19
2 6 20
2 7 21
3 1 22
3 2 23
3 3 24
3 4 25
3 5 26
3 6 27
3 7 28

I tried using crosstab but couldn't get it working right. Below is my tried approach -

select * from crosstab('select ID,week1, week2,week3,week4,week5,week6,week7 order by ID') as table_name(ID, week_number, week_value);

Please can someone help as I don't have much experience in handling complex sql queries.

Upvotes: 1

Views: 204

Answers (4)

Manny
Manny

Reputation: 87

select t.id
,unnest(array[1, 2, 3, 4, 5, 6, 7]) as week_number
,unnest(array[week1, week2, week3, week4, week5, week6, week7]) as week_value
from table1 t;

Upvotes: 0

blackbishop
blackbishop

Reputation: 32670

Since you tagged the question with SparkSQL, here's a solution using stack function. Applied to your example:

df = spark.createDataFrame([
    (1, 8, 9, 10, 11, 12, 13, 14),
    (2, 15, 16, 17, 18, 19, 20, 21),
    (3, 22, 23, 24, 25, 26, 27, 28)
], ["ID", "week1", "week2", "week3", "week4", "week5", "week6", "week7"])

df.createOrReplaceTempView("my_table")

spark.sql("""
SELECT  ID,
        stack(7, '1', week1, '2', week2, '3', week3, '4', week4, '5', week5, '6', week6, '7', week7) as (week_number, week_value)
FROM    my_table
""").show()

#+---+-----------+----------+
#| ID|week_number|week_value|
#+---+-----------+----------+
#|  1|          1|         8|
#|  1|          2|         9|
#|  1|          3|        10|
#|  1|          4|        11|
#|  1|          5|        12|
#|  1|          6|        13|
#|  1|          7|        14|
#|  2|          1|        15|
#|  2|          2|        16|
#|  2|          3|        17|
#|  2|          4|        18|
#|  2|          5|        19|
#|  2|          6|        20|
#|  2|          7|        21|
#|  3|          1|        22|
#|  3|          2|        23|
#|  3|          3|        24|
#|  3|          4|        25|
#|  3|          5|        26|
#|  3|          6|        27|
#|  3|          7|        28|
#+---+-----------+----------+

Upvotes: 1

Stefanov.sm
Stefanov.sm

Reputation: 13049

A quick alternative using JSONB that works for any number of colums provided that one of them is called id. Unrelated but the result looks alarmingly similar to the infamous EAV antipattern.

with t as (select to_jsonb(t) j from the_table t)
select j ->> 'id' id, 
       (jsonb_each_text(j - 'id')).*
from t;
id key value
1 week1 8
1 week2 9
1 week3 10
1 week4 11
1 week5 12
1 week6 13
1 week7 14
2 week1 15
2 week2 16
2 week3 17
2 week4 18
2 week5 19
2 week6 20
2 week7 21
3 week1 22
3 week2 23
3 week3 24
3 week4 25
3 week5 26
3 week6 27
3 week7 28

Upvotes: 1

user330315
user330315

Reputation:

This is actually the opposite of a pivot, also known as "unpivot" and can be done using a lateral cross join:

select t.id, x.*
from the_table t
  cross join lateral (
     values (1, week1), (2, week2), (3, week3), 
            (4, week4), (5, week5), (6, week6), 
            (7, week7)
  ) as x(week_number, week_value)
order by t.id, x.week_number

Upvotes: 2

Related Questions