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