0

In SQL (AGINITY, Amazon Redshift), I want to group by columns COL1, COL2, and COL3.

Then I want to transpose COL5 so that it will get the sum from COL4.

I think this has to do with converting from long format to wide format. I know how to do it in R but not in SQL. The values are not just A and B by the way. There are about 10 other ones because this is just a subset of many more rows.

I have a dataset as follows: 

  COL1   COL2       COL3   COL4    COL5
 alpha   beta      gamma     15       A
 alpha   beta      gamma     20       A
 alpha   beta    epsilon     30       A
 zeta    beta      gamma     18       B
 zeta   delta      gamma     13       B

I want to change it to: 

  COL1   COL2       COL3      A     B     C    D  .... 
 alpha   beta      gamma     35     0   ...  ...  ....
 alpha   beta    epsilon     30     0   ...  ...  ....
 zeta    beta      gamma      0    18   ...  ...  ....
 zeta   delta      gamma      0    13   ...  ...  ....

Thanks!

nak5120
  • 4,089
  • 4
  • 35
  • 94
  • are there only A,B in col5 or do you have many more values? – Vamsi Prabhala Jul 28 '17 at 21:03
  • There are more values – nak5120 Jul 28 '17 at 21:04
  • you should mention that in the question and show the expected output as well. – Vamsi Prabhala Jul 28 '17 at 21:05
  • just edited the question, also this is different I think because there is a group by involved – nak5120 Jul 28 '17 at 21:07
  • I don't think it's an exact duplicate. Anyway, you can do this - just replace the table name: select col1, col2, col3, ISNULL([A], 0) as [A], ISNULL([B], 0) AS [B] from ( select col1, col2, col3, col4, col5 from @tmp )src pivot ( sum(col4) for col5 in ([A],[B]) )pvt – Max Szczurek Jul 28 '17 at 21:31
  • If it's just 10 static columns, just hard code them, right? If it's a dynamic list, then you can dynamically build the query. I think @vkp probably assumed it was a dynamic list, and that's why he closed this as a duplicate, but that doesn't seem entirely clear. – Max Szczurek Jul 28 '17 at 21:44
  • @maxszczurek you're right. It is a dynamic query because when I run this it will not always be 10 values in that column. It could be 50. So I cannot hard code the 10 – nak5120 Jul 28 '17 at 22:46
  • I am using Aginity to run these queries and it looks like pivot is not an option. That is why I am having trouble with this. It is recommended to use the functions: DECODE and/or SYSTEM VIEWS. Still figuring it out. – nak5120 Jul 31 '17 at 14:18

0 Answers0