Suppose I have a table with 3 columns namely color1,color2,color3 with different color values in all the column.My task is to display the color which is the most repeating in all the three columns for each id.With the below table structure and data the o/p for id 1 should be 'red' and for id 2 should be 'green'
So here is the query on how to do it.declare @tbl as table(id int,color1 varchar(40),color2 varchar(40),color3 varchar(40))
insert into @tbl
select 1,'red','red','blue' union all
select 2,'green','red','green'
select T2.id,T1.color1,T1.color2,T1.color3,T2.col as MaxColor from @tbl T1 inner join
(
select *,ROW_NUMBER()over(PARTITION by id order by id )as rid from
(select * from @tbl)u
unpivot
(col for columns in (color1,color2,color3))v
)T2 on T1.id=T2.id where T2.rid=1
The trick here is to use unpivot & then by using ranking function assign an id which has the maximum number of color values for each distinct id & selecting the colors having rowid as 1.
