CODEWITHSUNDEEP
sql-server
rock
rock

Reputation: 45

In Pivot how to eliminate duplicates

Before Pivoting

 Year  | RainDays |
-------+----------+
 2012  |      112 |
 2013  |      116 |
 2014  |      111 |
 2015  |       80 |
 2016  |      110 |
 2017  |      102 |
 2018  |      80  |
 2019  |      110 |

After Pivoting

 2012 | 2013 | 2014 | 2015 | 2016 |  2017| 2018 | 2019 |  
 -----+------+------+------+------+------+------+------+
 112  |  116 |  111 |   80 |  110 |  102 |  0.0 |  0.0 |

If any number is repeating for second time in pivot it should be made 0.0

Below is the SQL for pivot

SELECT [1], [2], [3], [4], [5], [6], [7]    
FROM  
(
Select row_number () over(Order by [Year] asc) Rn,Raindays
from Rain
) AS SourceTable  
PIVOT  
(  
Max(Raindays)
FOR Rn IN ( [1], [2], [3], [4], [5], [6], [7])  
) AS PivotTable

Upvotes: 0

Views: 52

Answers (2)

PSK
PSK

Reputation: 17943

You can try like following using ROW_NUMBER() .

;with cte as
(
 select *, ROW_NUMBER() over(partition by raindays order by year) rn
 from @mytable
)
SELECT *
FROM  
(
 select Year, case when rn >1 then 0 else RainDays end as RainDays from cte 
)t 
PIVOT  
(  
max(RainDays)  
FOR Year IN ([2012], [2013], [2014], [2015], [2016],[2017],[2018],[2019])  
) AS PivotTable;

Online Demo

Output

+------+------+------+------+------+------+------+------+
| 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 |
+------+------+------+------+------+------+------+------+
| 112  | 116  | 111  | 80   | 110  | 102  | 0    | 0    |
+------+------+------+------+------+------+------+------+

Upvotes: 3

DarkRob
DarkRob

Reputation: 3833

You may try this

    create table #temptable ( name varchar(50), iid int )
    Go

    insert into #temptable ( name, iid )
    values ( '2012', 112 )
    ,( '2013', 215 )
    ,( '2014', 152 )
    ,( '2015', 112 )

    select * from #temptable


    ; with cte as (
    select row_number() over (partition by iid order by iid) as sl , * from #temptable)
    , ct as (
    select name, case when sl=1 then iid else 0 end as iid from cte)
    select * from (
    select name, iid from ct ) as d
    pivot ( max(iid) for name in ( [2012], [2013], [2014], [2015] ) ) as p

    go

    drop table #temptable

Upvotes: 0

Related Questions