PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, November 1, 2022

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

 November 01, 2022     pivot, snowflake-cloud-data-platform, sql     No comments   

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)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing