0

Here's the sample:

select * from tmp

--output
A       B       Value
---------------------
a       x       1
b       x       2
a       y       3
b       y       4
c       y       5

After a SQL command grouping on B column, I'd like to make each value of column A to be a separate column as illustrated below:

B       a       b       c
----------------------------
x       1       2       null
y       3       4       5

If there any specific terminology for this transformation? Thanks!

KAs
  • 1,818
  • 4
  • 19
  • 37
  • What's the expected result if someone suddenly inserts (e,v,6)? – jarlh Oct 27 '16 at 09:04
  • Then it should have another column named v, whose value is null to all x and y, except that for v it's 6. @jarlh – KAs Oct 27 '16 at 09:15

1 Answers1

1

You need to find max of other value and group it by with anchor column(b in your case). Please note that your column count should be similar to number of values expected in field A.

select b,
max(case when A='a' then Value else null end)a,
max(case when A='b' then Value else null end)b,
max(case when A='c' then Value else null end)c
from tmp
group by 1
Bhavesh Ghodasara
  • 1,981
  • 2
  • 15
  • 29
  • what if the value of column A is indefinite? Is there any way to do that without iterate through the value of A manually? – KAs Oct 27 '16 at 09:13
  • You can use cross tab function for that ; http://stackoverflow.com/questions/23060256/postgres-transpose-rows-to-columns – Bhavesh Ghodasara Oct 27 '16 at 09:26