SQL Server - Sql query help - Asked By sunny C on 16-Apr-13 07:45 AM

Hi There,

I am stuck at the below issue. can you please help me in giving me some idea.

Trying to query the below table.

Col1 Col2 Col3 Col4 Col5
v1 v2 v1 v1 v2
v1 v2 v3 v2 v1
v2 v2 v3 v2 v1
v3 v1 v2 v1 v1
v3 v1 v3 v1 v1
v3 v1 v3 v1 v1

To Extract below:
V1 V2 V3
Col1 33.3% 16.6% 50.0%
Col2 50.0% 50.0% 0.0%
Col3 16.6% 16.6% 66.6%
Col4 66.6% 33.3% 0.0%
Col5 83.3% 16.6% 0.0%

I need to generate query to retrieve the data in the above table. Can you give me some idea how to go about it.

Thanks,
Sunny

Sathish S replied to sunny C on 16-Apr-13 08:23 AM

Have a look into Pivot topic, you could change rows into columns and columns to rows with the help of Pivot function, you could also perform aggregate function on the values.
http://msdn.microsoft.com/en-in/library/ms177410(v=sql.105).aspx

Robbe Morris replied to sunny C on 16-Apr-13 08:23 AM

I don't believe you can use standard SQL to derive results like this. A PIVOT TABLE might support this but I'm not well versed in them.

sunny C replied to Sathish S on 16-Apr-13 09:11 AM

Hi Satish/Robbe,

I will check this Pivot table concept.

BTW Any idea how to calculate that % thing?

Thanks for your suggestion.

Regards,
Sunny

Sandeep Mittal replied to sunny C on 13-May-13 08:35 AM

declare @tab table( Col1 varchar(10), Col2 varchar(10), Col3 varchar(10), Col4 varchar(10), Col5 varchar(10))

insert into @tab

select 'v1','v2','v1','v1','v2' union all

select 'v1','v2','v3','v2','v1' union all

select 'v2','v2','v3','v2','v1' union all

select 'v3','v1','v2','v1','v1' union all

select 'v3','v1','v3','v1','v1' union all

select 'v3','v1','v3','v1','v1'

declare @cnt int

select @cnt = COUNT(1) from @tab

;with cte as(

select 'Col1' as Col , Col1 as ColValue, CAST(CAST(COUNT(1)*100/(@cnt*1.0) as numeric(4,1)) as varchar) + '%' as cnt from @tab group by Col1 union all

select 'Col2', Col2, CAST(CAST(COUNT(1)*100/(@cnt*1.0) as numeric(4,1)) as varchar) + '%' from @tab group by Col2 union all

select 'Col3', Col3, CAST(CAST(COUNT(1)*100/(@cnt*1.0) as numeric(4,1)) as varchar) + '%' from @tab group by Col3 union all

select 'Col4', Col4, CAST(CAST(COUNT(1)*100/(@cnt*1.0) as numeric(4,1)) as varchar) + '%' from @tab group by Col4 union all

select 'Col5', Col5, CAST(CAST(COUNT(1)*100/(@cnt*1.0) as numeric(4,1)) as varchar) + '%' from @tab group by Col5

)

select Col, isnull([v1],'0%') as v1 , isnull([v2],'0%') as v2, isnull([v3],'0%') as v3

from (

select * from cte

)src

pivot(max(cnt) for ColValue in ([v1], [v2], [v3])) pvt