How to convert rows into column values in SQL

Question

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

click this [TAG: Pivot](http://stackoverflow.com/questions/tagged/pivot) — John Woo, Jun 16 '14 at 07:26
possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) — timo.rieber, Jun 16 '14 at 07:29
possible duplicate of [How to use GROUP\_CONCAT in a CONCAT in mysql](http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql) — , Jun 16 '14 at 07:30
Check the post http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — Basil Farook, Jun 16 '14 at 09:39

score 1 · Answer 1 · answered Jun 16 '14 at 07:30

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.

score 1 · Answer 2 · edited Jun 16 '14 at 16:05

1

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

edited Jun 16 '14 at 16:05

Barranka

20,547
13
65
83

answered Jun 16 '14 at 07:33

Friyank

469
3
8

I think you mean SQL server... am I right? Remember: SQL is short for "structured query language", not for a specific RDBMS. – Barranka Jun 16 '14 at 07:41
ya, its for SQL Server only, Use Pivot table, and convert rows into column name – Friyank Jun 16 '14 at 08:40

score 1 · Answer 3 · answered Jun 16 '14 at 09:04

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

How to convert rows into column values in SQL

3 Answers3