1

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!

lemon
  • 14,875
  • 6
  • 18
  • 38
catty_catto
  • 13
  • 3
  • Does this answer your question? [How to Pivot table in BigQuery](https://stackoverflow.com/questions/26272514/how-to-pivot-table-in-bigquery) – lemon Jun 06 '23 at 09:38
  • i think it's different to my case?, my data is in string type and didn't need to SUM and need to show the null result – catty_catto Jun 06 '23 at 09:54
  • Assign row numbers, then you can pivot, like [here](https://dbfiddle.uk/YebT8oFc). – Ponder Stibbons Jun 06 '23 at 10:09
  • I think I got the right result from @.lemon and @.PonderStibbons. Many many thanks! – catty_catto Jun 06 '23 at 10:15
  • So, what you want to select is really four independent lists (Dom, Anggora, Turk, Persian) and show them side by side. In other words: You select four columns, but the rows don't have a meaning. You could see 'coky' and 'chee' in a row just as well as 'coky' and 'haina' - you don't care. This is a very uncommon task for SQL. – Thorsten Kettner Jun 06 '23 at 10:17
  • yes that's true I don't care about the rows part, and it made me confused about how to solve it (because I don't have any id). Btw thanks a lot! @ThorstenKettner – catty_catto Jun 06 '23 at 10:22

1 Answers1

0

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'))
lemon
  • 14,875
  • 6
  • 18
  • 38