Pivot String Values in Snowflake

Question

How can I pivot this table

ID	attribute_name	attribute_value
1	Name	John
1	Country	UK
1	City	London

into structure?

ID	Name	Country	City
1	John	UK	London

According to the documentation pivot requires a aggregate function

 SELECT ...
   FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )

How can I apply this to string values?

Answer 1

The aggregating function can be max(). For example:

select * 
from (
    select xx.seq, xx.value:"@id" id, xx.value:"$" title
    from BooksXML, table(flatten(xml:"$":"$")) xx
)
pivot(max(title) for id in ('bk101', 'bk102', 'bk103', 'bk104', 'bk105')) as p
order by seq

With the table:

CREATE temp TABLE BooksXML
as
select parse_xml('<catalog issue="spring">
  <Books>
    <book id="bk101">The Good Book</book>
    <book id="bk102">The OK Book</book>
    <book id="bk103">The NOT Ok Book</book>
    <book id="bk104">All OK Book</book>
    <book id="bk105">Every OK Book</book>
  </Books>
</catalog>') xml
union all select parse_xml('
<catalog issue="spring">
  <Books>
    <book id="bk102">The OK Book1</book>
    <book id="bk103">The NOT Ok Book1</book>
    <book id="bk104">All OK Book1</book>
  </Books>
</catalog>')
union all select parse_xml('
<catalog issue="spring">
  <Books>
    <book id="bk101">The Good Book2</book>
    <book id="bk103">The NOT Ok Book2</book>
    <book id="bk104">All OK Book2</book>
    <book id="bk105">Every OK Book2</book>
  </Books>
</catalog>');