transform columns to rows

Question

I have a table table1 like below

+----+------+------+------+------+------+
| id | loc  | val1 | val2 | val3 | val4 |
+----+------+------+------+------+------+
|  1 | loc1 |   10 | 190  | null |   20 |
|  2 | loc2 |   20 | null | 10   |   10 |
+----+------+------+------+------+------+

need to combine the val1 to val4 into a new column val with a row for each so that the output is like below.

NOTE: - I data I have has val1 to val30 -> ie. 30 columns per row that need to be converted into rows.

+----+------+--------+
| id | loc  |  val   |
+----+------+--------+
|  1 | loc1 |   10   |
|  1 | loc1 |   190  |
|  1 | loc1 |   null |
|  1 | loc1 |   20   |
|  2 | loc2 |   20   |
|  2 | loc2 |   null |
|  2 | loc2 |   10   |
|  2 | loc2 |   10   |
+----+------+--------+

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

You could use a cross join with generate_series for this:

select
    id,
    loc,
    case x.i
        when 1 then val1 
        when 2 then val2
        . . .
    end as val
from t 
cross join generate_series(1, 4) x (i)

It uses the table only once and can be easily extended to accommodate more columns.

Demo

Note: In the accepted answer, first approach reads the table many times (as many times as column to be unpivoted) and second approach is wrong as there is no UNPIVOT in postgresql.

score 1 · Answer 2 · edited May 23 '17 at 11:46

You can use lateral join for transform columns to rows :

SELECT a.id,a.loc,t.vals
FROM table1 a,
unnest(ARRAY[a.val1,a.val2,a.val3,a.val4]) t(vals);

If you want to this with a dynamic added columns:

CREATE OR REPLACE FUNCTION columns_to_rows(
 out id integer,
 out loc text,
 out vals integer
)
RETURNS SETOF record AS
$body$
DECLARE

columns_to_rows text;

BEGIN

 SELECT string_agg('a.'||attname, ',') into columns_to_rows
 FROM pg_attribute
 WHERE attrelid = 'your_table'::regclass AND --table name
 attnum > 0 and --get just the visible columns
 attname <> all (array [ 'id', 'loc' ]) AND --exclude some columns
 NOT attisdropped ; --column is not dropped

 RETURN QUERY
 EXECUTE format('SELECT a.id,a.loc,t.vals
 FROM your_table a, 
 unnest(ARRAY[%s]) t(vals)',columns_to_rows);

end;
$body$
LANGUAGE 'plpgsql'

Look at this link for more detail: Columns to rows

beejm · Accepted Answer · 2017-03-20T04:49:53.097

0

I'm sure there's a classier approach than this.

SELECT * FROM (
select id, loc, val1 as val from #t a
UNION ALL
select id, loc, val2 as val from #t a
UNION ALL
select id, loc, val3 as val from #t a
UNION ALL
select id, loc, val4 as val from #t a
) x
order by ID

Here's my attempt with unpivot but cant get the nulls, perhaps perform a join for the nulls? Anyway i'll still try

SELECT *
FROM (
SELECT * FROM #t
) main
UNPIVOT (
    new_val 
    FOR val IN (val1, val2, val3, val4) 
) unpiv

edited Mar 20 '17 at 04:49

answered Mar 20 '17 at 04:33

beejm

2,381
1
10
19

1

He has 30 columns (which he again did not tell us). A better approach might be to use cross tabs. @a_horse_with_no_name could figure this one out. – Tim Biegeleisen Mar 20 '17 at 04:34
I'm actually trying out the cross join approach right now haha @TimBiegeleisen – beejm Mar 20 '17 at 04:35
1

Postgres dont support UNPIVOT. – Gurwinder Singh Mar 20 '17 at 15:15
@BennjoeMordeno - can you look at GurV's comment and possibly fix the second part of the answer ? – user3206440 Mar 21 '17 at 05:24

Prahalad Gaggar · Answer 4 · 2017-03-20T05:00:31.293

It will not work in postgress as needed by user. Saw when it was mentioned in comments.

I am finding a way to handle "NULL"

select p.id,p.loc,CASE WHEN p.val=0 THEN NULL ELSE p.val END AS val
from 
(
    SELECT id,loc,ISNULL(val1,0) AS val1,ISNULL(val2,0) AS val2,ISNULL(val3,0) AS val3,ISNULL(val4,0) AS val4
    FROM Table1
)T
unpivot
(
  val
  for locval in(val1,val2,val3,val4)
)p

Test

EDIT:

Best Solution from my Side:

select a.id,a.loc,ex.val
from (select 'val1' as [over] union all select 'val2' union all select 'val3'
        union all select 'val1' ) pmu
cross join (select id,loc from Table1) as a
left join
Table1 pt
unpivot
(
 [val]
 for [over] in (val1, val2, val3, val4)
) ex
on pmu.[over] = ex.[over] and
   a.id = ex.id

Test

transform columns to rows

4 Answers4

Demo