Unpivot postgres table having multiple columns-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

Since you tagged the question with SparkSQL, here's a solution using stack function:

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

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(above_query).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|
# --- ----------- ----------

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

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

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