CODEWITHSUNDEEP
sqlsql-server
Nayana
Nayana

Reputation: 1549

SQL Server: how to turn rows into columns

I have an output that looks like this:

colA | colB | Value
A    | a    | 46
A    | b    | 8979
A    | C    | 684168468
B    | a    | 68546841
B    | b    | 456846
B    | c    | 468468
C    | a    | 684684
.    | .    |  .
.    | .    |  .

The list goes on and on. Colb repeats a sequence of a,b,c, and there could be duplicate values, but I guess it doesn't matter, since it will have different values for colB.

I want to make it look like this

col A | a        | b      | c
A     | 46       | 8979   | 684168468
B     | 68546841 | 456846 | 468468
C     | 684684

I know I can do it with pivot table, but I want to do it in sql server, because I have so many rows. Can anyone tell me how to accomplish this?

Upvotes: 1

Views: 266

Answers (2)

Eralper
Eralper

Reputation: 6622

It is also possible to improve Pawel's solution by using dynamic SQL pivot query as follows

This will take task of you to create the column list from possible data values from second column

DECLARE @values varchar(max)
SELECT @values =
 STUFF(
 (
  select distinct ',[' + col2 + ']'
  from tblData
  for xml path('')
 ),
 1,1,'')

DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select
 *
from tblData
PIVOT (
 sum(val)
 FOR col2
 IN (
  ' + @values + '
 )
) PivotTable 
'
--print @SQL
exec sp_executesql @SQL

Output is as seen in below screenshot enter image description here

For sample data:

create table tblData (col1 varchar(5),col2 varchar(5),val bigint)
insert into tblData values
    ('A', 'a', 46       ),
    ('A', 'b', 8979     ),
    ('A', 'C', 684168468),
    ('B', 'a', 68546841 ),
    ('B', 'b', 456846   ),
    ('B', 'c', 468468   ),
    ('C', 'a', 684684   )

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

Simple PIVOT:

SELECT *
FROM YourTable
PIVOT (SUM(Value) FOR colB IN (a,b,c)) P

Example

WITH Src AS
(
    SELECT * FROM (VALUES
    ('A', 'a', 46       ),
    ('A', 'b', 8979     ),
    ('A', 'C', 684168468),
    ('B', 'a', 68546841 ),
    ('B', 'b', 456846   ),
    ('B', 'c', 468468   ),
    ('C', 'a', 684684   )) T(colA, colB, Value)
)
SELECT *
FROM Src
PIVOT
(SUM(Value) FOR colB IN (a,b,c)) P

Result:

colA a           b           c
---- ----------- ----------- -----------
A    46          8979        684168468
B    68546841    456846      468468
C    684684      NULL        NULL

Upvotes: 3

Related Questions