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?
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>');