I am using Redshift SQL.
I would like to transpose the distinct rows of “Id1” in “Table” into columns.
Thus the output should be like the bottom most table, providing weights as columns for each “Id2”.
Or Alternatively, concatenate weights into one column.
Any guidance on functions or methods to use will be greatly appreciated.
Table
| Id1 | Id2 | Weight |
|---|---|---|
| 1 | 1 | 55 |
| 1 | 2 | 65 |
| 2 | 1 | 78 |
| 3 | 1 | 70 |
| 3 | 2 | 90 |
| 3 | 3 | 82 |
| 4 | 4 | 44 |
| 4 | 4 | 51 |
Wanted Output
| Id1 | Weight |
|---|---|
| 1 | 55-65 |
| 2 | 78 |
| 3 | 70-90-2 |
| 4 | 44-51 |
Advertisement
Answer
You can use LISTAGG() to do this:
SELECT Id1, LISTAGG(Weight, '-') WITHIN GROUP (ORDER BY Id2) FROM yourtable GROUP BY Id1;