enter image description here
I have only one table to work wit and don’t want to create new tables.
For example in the screenshot: I want to combine rows based off a unique value in column A and turn it into the table on the right.
How can I write a SQL statement to do this?
I am using SQL in Snowflake.
Thanks!
I’ve tried researching online but can’t find an example specific to my needs.
3
SELECT
"account ID",
MAX(CASE WHEN rn = 1 THEN "question ID" END) AS "question ID (1)",
MAX(CASE WHEN rn = 2 THEN "question ID" END) AS "question ID (2)",
MAX(CASE WHEN rn = 3 THEN "question ID" END) AS "question ID (3)",
MAX(CASE WHEN rn = 1 THEN "status" END) AS "status (1)",
MAX(CASE WHEN rn = 2 THEN "status" END) AS "status (2)",
MAX(CASE WHEN rn = 3 THEN "status" END) AS "status (3)"
FROM (
SELECT "account ID", "question ID", "status",
ROW_NUMBER() OVER (PARTITION BY "account ID" ORDER BY "question ID") AS rn
FROM your_table_name
) AS subquery
GROUP BY "account ID";
This will match your requirement.
New contributor
Krutarth is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.