Issue
I would like to pivot table, but to use PIVOT() have to use any aggregation functions such as max(), min(), mean(), count(), sum(). I don't need use these functions, but I need to transform my table without using them.
Source table
SOURCE | ATTRIBUTE | CATEGORY |
---|---|---|
MOVIES | 1 | |
YAHOO | JOURNAL | 2 |
MUSIC | 1 | |
AOL | MOVIES | 3 |
The new table should be like this:
ATTRIBUTE | YAHOO | AOL | |
---|---|---|---|
MOVIES | 1 | 3 |
Will be grateful if someone would help me.
Solution
The pivot syntax requires an aggregate function. It's not optional.
https://docs.snowflake.com/en/sql-reference/constructs/pivot.html
SELECT ... FROM ... PIVOT ( <aggregate_function> ( <pivot_column> ) FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )
[ ... ]
In the Snowflake documentation, optional syntax is in square braces. The <aggregate_function>
is not in square braces, so it's required. If you only have one value, any of the aggregate functions you listed except count will work and give the same result.
create or replace table T1("SOURCE" string, ATTRIBUTE string, CATEGORY int);
insert into T1("SOURCE", attribute, category) values
('GOOGLE', 'MOVIES', 1),
('YAHOO', 'JOURNAL', 2),
('GOOGLE', 'MUSIC', 1),
('AOL', 'MOVIES', 3);
select *
from T1
PIVOT ( sum ( CATEGORY )
for "SOURCE" in ( 'GOOGLE', 'YAHOO', 'AOL' ));
ATTRIBUTE | 'GOOGLE' | 'YAHOO' | 'AOL' |
---|---|---|---|
MOVIES | 1 | null | 3 |
MUSIC | 1 | null | null |
JOURNAL | null | 2 | null |
Answered By - Greg Pavlik Answer Checked By - Cary Denson (PHPFixing Admin)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.