How to pivot table without using aggregate function SQL?

Question

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.

@Sergey I tried to add the desired output, but something went wrong with markdown. Now jarlh added the one row for desired output. Thanks. — Alex, Aug 02 '22 at 10:51
If you consider your data, you will see that an aggregation is required. You start out with multiple rows where ATTRIBUTE = 'MOVIES' but your output requires a single row. You are 'aggregating' by ATTRIBUTE. — Dave Welden, Aug 02 '22 at 13:23
Does this answer your question? [TSQL Pivot without aggregate function](https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function) — Umut TEKİN, Aug 03 '22 at 14:19

Greg Pavlik · Accepted Answer · 2022-08-02T13:38:07.500

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

Adrian White · Answer 2 · 2022-08-03T01:13:29.223

Here's an alternative approach utilising ARRAY_AGG() which maybe more flexible. For example if you wanted to pivot the Attribute instead of the category, easy peasy. The 'category' here appears to be more of a classification or label instead of something we'd want summed.

The array doesn't need to be constrained any particular data type. This can be extended to pivots with many objects.

WITH CTE AS (  SELECT 'GOOGLE' SOURCE, 'MOVIES' ATTRIBUTE, 1 CATEGORY UNION ALL SELECT 'YAHOO', 'JOURNAL', 2 UNION ALL SELECT 'GOOGLE', 'MUSIC', 1 UNION ALL SELECT 'AOL', 'MOVIES', 3 );

SELECT
    ATTRIBUTE, GOOGLE[0] GOOGLE, YAHOO[0] YAHOO, AOL[0] AOL 
FROM
    CTE PIVOT (ARRAY_AGG(CATEGORY) FOR SOURCE IN ('GOOGLE', 'YAHOO', 'AOL')
) AS A (ATTRIBUTE, GOOGLE, YAHOO, AOL);

How to pivot table without using aggregate function SQL?

2 Answers2