Transpose column to row without any 'id' or key column based on row number

Question

Hi I have table like this

name	cat_type
coco	Dom
coky	Anggora
chee	Turk
eena	Persian
onaa	Dom
haina	Turk
haani	Dom

I want to try to transform to this

Dom	Anggora	Turk	Persian
coco	coky	chee	eena
onaa	null	haina	null
haani	null	null	null

I have tried the following approach:

SELECT *
FROM my_table
PIVOT( STRING_AGG(name) FOR cat_type IN ('Dom','Anggora','Turk','Persian'))   

But I didn't get what I want, as I got result like the following:

Dom	Anggora	Turk	Persian
coco,onaa,haani	coky	chee,haina	eena

Is there something wrong in my query?

Thank you in advance!

Answer 1

This problem looks like a slightly variation of classical pivot. What you're missing is the column you're grouping by, which in your case is the row number.

WITH cte AS(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY cat_type ORDER BY name) AS rn
    FROM my_table
)
SELECT *, 
FROM cte
PIVOT (STRING_AGG(name) FOR cat_type IN ('Dom','Anggora','Turk','Persian'))