CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Dev
Dev

Reputation: 367

Get Pivot values on specific condition

I have query in SQL SEVRER -

SELECT  alarm,annual_calendar,chronograph,day_flag,equation_of_time,flyback_chronograph,
gmt_time,jumping_hour,minute_repeater,moon_phase,perpetual_calendar,power_reserve,seconds_flag
,split_seconds_chrono,tachymeter,tourbillon,ultra_thin,world_time,catalog_item_id
FROM catalog_item_watches where alarm is not null
order by id desc

which return output like

enter image description here

And i want result like

enter image description here

Thanks in advance.

Upvotes: 1

Views: 175

Answers (1)

gotqn
gotqn

Reputation: 43636

First make UNPIVOT, then exclude the N values. After that make a PIVOT. You may want to make it dynamic.

It should be something like this:

SELECT *
FROM
(
    SELECT [catalog_item_id]
          ,[column]
          ,'Web' + CAST(DENSE_RANK() OVER(PARTITION BY [catalog_item_id] ORDER BY [column]) AS VARCHAR(12))
    FROM
    (
        SELECT [catalog_item_id], [alarm], [annual_calendar], [chronograph], [day_flag], [equation_of_time], [flyback_chronograph], [ gmt_time], [jumping_hour], [minute_repeater], [moon_phase], [perpetual_calendar], [power_reserve], [seconds_flag], [split_seconds_chrono], [tachymeter], [tourbillon], [ultra_thin], [world_time]
        FROM catalog_item_watches 
        where [alarm] is not null
    ) DS
    UNPIVOT
    (
        [value] FOR [column] IN ([alarm], [annual_calendar], [chronograph], [day_flag], [equation_of_time], [flyback_chronograph], [ gmt_time], [jumping_hour], [minute_repeater], [moon_phase], [perpetual_calendar], [power_reserve], [seconds_flag], [split_seconds_chrono], [tachymeter], [tourbillon], [ultra_thin], [world_time])
    ) UNPVT
    WHERE UNPVT.[value] = 'Y'
) DataSource ([catalog_item_id], [column_value], [column_name])
PIVOT
(
    MAX([column_value]) FOR [column_name] IN ([web1], [web2], [web3], [web4], [web5], [web6], [web7], [web8], [web9], [web10], [web11], [web12], [web13], [web14], [web15], [web16], [web17], [web18])
) PVT

Upvotes: 1

Related Questions