CODEWITHSUNDEEP
sqlpivot
D.Jay
D.Jay

Reputation: 1

Pivot in SQL without Aggregate function

I have a scenario Where I have a table like

Table View

and What Output I want is

Upvotes: 0

Views: 1001

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270051

You can "pivot" such data using joins:

select p.productname,
       t_win32.grade as win32,
       t_universal.grade as universal,
       . . .
from products p left join -- assume you have such a table
     t t_win32
     on t_win32.product_name = p.productname and t_win32.platform = 'Win32' left join
     t t_universal
     on t_universal.product_name = p.productname and t_universal.platform = 'Universal'  left join
     . . .

If you don't have a table products, use a derived table instead:

from (select distinct product_name from t) p left join
     . . .

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

If your argument is "I will only ever have one value or no values, therefore I don't want an aggregate", realise that there are several aggregates that, if they're only passed a single value to aggregate, will return that value back as their result. MIN and MAX come to mind. SUM also works for numeric data.

Therefore the solution to specifying a PIVOT without an aggregate is instead to specify such a "pass through" aggregate here.

Basically, PIVOT internally works a lot the same as GROUP BY. Except the grouping columns are all columns in the current result set other than the column mentioned in the aggregate part of the PIVOT specification. And just as with the rules for the SELECT clause when GROUP BY is used1, every column either needs to be a grouping column or contained in an aggregate.

1Grumble, grumble, older grumble. Although the defaults are more sensible from 5.7.5 up.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try this:

Demo

with cte1 as
(
select 'Web' as platformname,'abc' as productname,'A' as grade
union all
select 'Web' ,'cde' ,'B'
union all
select 'IOS' ,'xyz' ,'C'
union all
select 'MAX' ,'cde' ,'D'
)
select  productname,[Web], [IOS], [Android],[Universal],[Mac],[Win32]
from    cte1 t
        pivot
        (
            max(grade)
            for platformname in ([Web], [IOS], [Android],[Universal],[Mac],[Win32])
        ) p

Upvotes: 0

Related Questions