Transpose Data Source Table

Question & Answer
,
1

I have a table with the following structure…

CREATE table1 (
date date,
company varchar(30),
cohort_index text,
percentage float

The output is…
date | company | cohort_index | percentage
2023-01-01 | abc | 0 | 1
2023-01-01 | abc | 1 | .54
2023-01-01 | abc | n | x

I would like to transpose this table to look like this…
date | company | 0 | 1 | n
2023-01-01 | abc | 1 | .54 | x
2023-01-01 | abc | 1 | .3 | y
2023-01-01 | abc | 1 | .6 | z

To be clear, I am not looking to do this as a table visual, this will become a line chart so I assume I can do this to the data table.

In case I am stuck trying to solve this with one solution when there is another, here is a quick explanation of my end goal. I want to plot multiple series on a line graph; the x-axis is the cohort_index (0 - 90), the y-axis is the percentage, the values are the percentages for each cohort index.

2

You can’t do this in quicksight. This will need to be done at the SQL level.

Here is a resource for that.

https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

3

Thank you! I also appreciate you taking the time to link an alternate solution.

1 Like