How to pivot table without using aggregate function SQL?-CodePudding

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.

CodePudding user response：

The pivot syntax requires an aggregate function. It's not optional.

The array doesn't need to be constrained any particular data type.

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