Need to decompose a column to multiple columns based on unique values in a table?

Question

Please help me with suitable ways to do this in Excel and SQL/Redshift.

So suppose I have a table like :

Original Table

I want to decompose this column 5, to as many columns as unique values in that column and put corresponding value of column 6 in the rows. Basically I want only one row for unique [column1, column2, column3] pair. So, I want the result like :

Required Table

Please don't link to images of data. Please copy the data in to your question and use the markdown control to format it. This way we can see the data in the context of the question, the input and output next to each other, and copy the data in to an app such as SQL Fiddle if necessary. Also, are you trying to do this in Excel or in RedShift? — MatBailie

MatBailie MatBailie · Accepted Answer · 2018-09-04T15:05:07

A simple SQL way to accomplish this would be to use MAX() together with CASE...

SELECT
  column1,
  column2,
  column3,
  column4,
  MAX(CASE WHEN column5 = 'val1' THEN column6 END)   AS val1,
  MAX(CASE WHEN column5 = 'val2' THEN column6 END)   AS val2,
  MAX(CASE WHEN column5 = 'val3' THEN column6 END)   AS val3
FROM
  yourTable
GROUP BY
  column1,
  column2,
  column3,
  column4

In the CASE expressions, if column5 doesn't match, the result of the CASE is implicitly NULL.

Then, in the MAX(), any NULL values are effectively ignored.

This causes the combination to pick the highest value of column6 where column5 is val1 / val2 / val3. Assuming there is only one such match, it picks that one matching value.

Need to decompose a column to multiple columns based on unique values in a table?

1 Answers