I need a help to convert sql table rows into columns.
Shown below is the sample data:
Col1 Col2 Col3
1 A 12
1 B 23
1 C 43
2 A 32
2 B 54
2 C 76
I want to convert this into:
Col1 A B C
1 12 23 43
2 32 54 76
Can someone please tell me how to achieve this. I have never done such queries before.
Thanks in advance , Vinay
A solution that works with MySQL:
select
col1,
sum(case col2 when 'A' then col3 end) as A,
sum(case col2 when 'B' then col3 end) as B,
sum(case col2 when 'C' then col3 end) as C
from
yourTable
group by
col1
Also, read this article.
create table #t(Col1 int,Col2 varchar(1),Col3 int)
insert into #t values(1,'A', 12),
(1,'B',23),
(1,'C',43),
(2,'A',32),
(2,'B',54),
(2,'C',76)
-- sql server solution
select * from #t
pivot
(
max(col3)
for col2 in([A],[B],[c])
)as piv;
--sql server with dynamic sql example
declare @cols nvarchar(max);
declare @query nvarchar(max);
select @cols=stuff((select distinct ','+QUOTENAME(col2) from #t for xml path(''),TYPE).value('.','nvarchar(max)') ,1,1,'');
select @query='select * from #t
pivot
(
max(col3)
for col2 in('+@cols+')
)as piv;'
exec(@query)
--this might work in sql-server mysql oracle or other RDBMS? (as answered by Barranka)
select
col1,
sum(case col2 when 'A' then col3 end) as A,
sum(case col2 when 'B' then col3 end) as B,
sum(case col2 when 'C' then col3 end) as C
from
#t
group by
col1
You can Create a Pivot Table in SQL Server
Select col1, col2, col3, col4
from tablname
Pivot
(
Sum(col) for tablname in ([col2],[col3],[col4])
)
as PivotTable
