SQL query to transpose data in below case

Question

I have the following SQL Server 2008 TABLE:

Heading     Limit   Package
X Charges   200     A
X Charges   300     B
X Charges   400     C
X Charges   500     D
Y Charges   550     A
Y Charges   450     B
Y Charges   350     C
Y Charges   250     D

Now I would like to get data in following representation:

Heading    Package_A   Package_B  Package_C  Package_D
X Charges  200         300        400        500
Y Charges  550         450        350        250

[possible](http://stackoverflow.com/questions/11985796/sql-server-pivot-dynamic-columns-no-aggregation/11985946#11985946) [duplicate](http://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) — StuartLC, Mar 10 '14 at 12:50

TechDo · Accepted Answer · 2014-03-10T12:54:07.030

2

Please try:

SELECT
  Heading, 
  A Package_A,
  B Package_B,
  C Package_C,
  D Package_D
FROM (
      SELECT * FROM YourTable
) up 
PIVOT (sum(Limit) FOR Package IN (A, B, C, D)) AS pvt

SQL Fiddle

edited Mar 10 '14 at 12:54

answered Mar 10 '14 at 12:45

TechDo

18,398
3
51
64

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

Fiddle here

SELECT 
    Heading, 
    [A] as Package_A, 
    [B] as Package_B, 
    [C] as Package_C, 
    [D] as Package_D
FROM
   TableX
PIVOT
(
  SUM(LIMIT)
  FOR Package IN ([A], [B], [C], [D])
) x;

The dynamic case (where the values of package are not known up front) is addressed here

edited May 23 '17 at 10:24

Community

1
1

answered Mar 10 '14 at 12:46

StuartLC

104,537
17
209
285

score 0 · Answer 3 · answered Mar 10 '14 at 12:48

Please, update a table name where is from table at below :

declare
    @cols nvarchar(max),
    @stmt nvarchar(max)

select @cols = isnull(@cols + ', ', '') + '[' + T.Package+ ']' 
     from (select distinct Package from TABLE1) as T

select @stmt = '
    select *
    from TABLE1 as T
        pivot 
        (
            max(T.VALUE)
            for T.Package in (' + @cols + ')
        ) as P'

exec sp_executesql  @stmt = @stmt

score 0 · Answer 4 · answered Mar 10 '14 at 12:50

Declare @t table( Heading varchar(50), Limit int,Package varchar(20)) 
insert into @t
select    'X Charges',   200,'A' union all
select  'X Charges',   300,'B' union all
select  'X Charges',   400,'C' union all
select  'X Charges',   500,'D' union all
select  'Y Charges',   550,'A' union all
select  'Y Charges',   450,'B' union all
select  'Y Charges',   350,'C' union all
select  'Y Charges',   250,'D'

select * from
(select * from @t)t4
pivot (min(limit) for Package in([A],[B],[C],[D]))pvt

score 0 · Answer 5 · edited Jul 22 '15 at 15:54

In order to do this dynamically:

Declare
@cols varchar(max)
select @cols =  stuff(
 (select distinct ',' + quotename(package)
from data
for xml path (''), type
).value('.', 'varchar(max)'), 1,1, '')  

declare 
@query varchar(max)

set @query = 'select distinct heading, ' + @cols + ' from
        (
            select   heading, Limit, Package
            from    data

        ) X
        pivot
        (
              MAX(Limit)
              for   Package in ('+@cols + ')
        ) p'

exec(@query)

Demo: http://sqlfiddle.com/#!3/8a42a/4

SQL query to transpose data in below case

5 Answers5