SQL: "Reverse" transpose a table

Question

I saw a lot of questions on transposing from the below table...

scanid | region | volume
-------------------------
1          A      34.4
1          B      32.1
1          C      29.1
2          A      32.4
2          B      33.2
2          C      35.6

to this table.

scanid | A_volume | B_volume | C_volume
----------------------------------------
1        34.4         32.1      29.1
2        32.4         33.2      35.6

However, I need to do the inverse, and have trouble trying to wrap my head around this problem. Can anyone help?

Thank you.

Once you get the inverse, make that your table and delete the other one. — Strawberry
– Strawberry, Commented Jun 1, 2017 at 7:51

Vao Tsun · Accepted Answer · 2017-06-01 08:58:18Z

2

it is not clear how you restore "A", "B", "C" values, so I just add them

prepare:

t=# create table s188 (scanid int,a float, b float,c float);
CREATE TABLE
t=# insert into s188 select 1,2,3,4;
INSERT 0 1
t=# insert into s188 select 2,12,13,14;
INSERT 0 1
t=# select * from s188;
 scanid | a  | b  | c
--------+----+----+----
      1 |  2 |  3 |  4
      2 | 12 | 13 | 14
(2 rows)

select:

t=# with a as (
  select scanid,unnest(array[a,b,c]) from s188
)
select scanid,chr((row_number() over (partition by scanid))::int + 64),unnest
from a;
 scanid | chr | unnest
--------+-----+--------
      1 | A   |      2
      1 | B   |      3
      1 | C   |      4
      2 | A   |     12
      2 | B   |     13
      2 | C   |     14
(6 rows)

and more neat solution from a_horse_with_no_name

t=# with a as (
  select scanid, x.*
  from s188, unnest(array[a,b,c]) with ordinality as x(volume,idx)
)
select scanid,
       chr(idx::int + 64) as region,
       volume
from a;
 scanid | region | volume
--------+--------+--------
      1 | A      |      2
      1 | B      |      3
      1 | C      |      4
      2 | A      |     12
      2 | B      |     13
      2 | C      |     14
(6 rows)

edited Jun 1, 2017 at 8:58

answered Jun 1, 2017 at 7:48

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jake Over a year ago

interesting solution, would have used this if not for the issue on "restoring" the column names. Appreciate the help!

JeffUK Over a year ago

I downvoted on the simple basis that it doesn't fully answer the question. By your own admission! If you could find a way to retain the region column it would be a more concise solution than mine.

user330315 Over a year ago

A simple column alias chr (...) as region and unnest as volume would do, wouldn't it? You also don't need a window function. See here: rextester.com/LWI50512

JeffUK · Accepted Answer · 2017-06-01 07:42:28Z

1

You could do this very simply with a UNION clause:

Select Scan_ID, 'A' as Region, A_Volume as volume
    union all
Select Scan_ID, 'B' as Region, B_Volume as volume
    union all
Select Scan_ID, 'C' as Region, C_Volume as volume

answered Jun 1, 2017 at 7:42

JeffUK

4,2912 gold badges22 silver badges35 bronze badges

1 Comment

Jake Over a year ago

of course right, I am really over thinking this problem~ thanks :)

Collectives™ on Stack Overflow

SQL: "Reverse" transpose a table

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related