[FIXED] How to pivot table without using aggregate function SQL?

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
GOOGLE	MOVIES	1
YAHOO	JOURNAL	2
GOOGLE	MUSIC	1
AOL	MOVIES	3

The new table should be like this:

ATTRIBUTE	GOOGLE	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)