Transforming shape of a SQL Table using Pivot

问题:

I have some data stored in a table in the following format

BatchID              EntityChanged             ChangeValue

EERS                  ABC                       DEF
EERS                  ABCD                      XYZ
EERS                  Something                 SomeValue
New Batch             SomethingMore             GHI

The list of values that can appear in the "EntityChanged" columns is finite, known beforehand and has no spaces etc in it. For the sake of argument let us say that this list is - ABC, ABCD, Something, SomethingMore

Then for the above dataset I would like an output of

BatchID            ABC              ABCD            Something       SomethingMore
EERS               DEF              XYZ             SomeValue       NULL   
New Batch          NULL             NULL            NULL            GHI

Using Pivot I could only go so far.

Can someone please help me slice this data in the desired way?

回答1:

Try this:

SELECT BatchID, 
   MAX(CASE WHEN EntityChanged = 'ABC' THEN ChangeValue END) 'ABC',
   MAX(CASE WHEN EntityChanged = 'ABCD' THEN ChangeValue END) 'ABCD',
   MAX(CASE WHEN EntityChanged = 'Something' THEN ChangeValue END) 'Something',
   MAX(CASE WHEN EntityChanged = 'SomethingMore' THEN ChangeValue END) 'SomethingMore'
FROM YourTable t 
GROUP BY BatchID

DEMO

回答2:

You can use a PIVOT for this, either via Static PIVOT or a Dynamic PIVOT which might be good if you have a unknown number of columns.

Static (See SQL Fiddle with Demo):

create table t2
(
    batchid varchar(10),
    entitychanged varchar(20),
    changevalue varchar(10)
)

insert into t2 values ('EERS', 'ABC', 'DEF')
insert into t2 values ('EERS', 'ABCD', 'XYZ')
insert into t2 values ('EERS', 'Something', 'SomeValue')
insert into t2 values ('New Batch', 'SomethingMore', 'GHI')

select *
from 
(
    select batchid, entitychanged, changevalue
    from t2
) x
pivot
(
    min(changevalue)
    for entitychanged in ([ABC], [ABCD], [Something], [SomethingMore])
) p

Dynamic (see SQL Fiddle with Demo):

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(entitychanged) 
                    from t2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT batchid, ' + @cols + ' from 
             (
                 select batchid, entitychanged, changevalue
                 FROM   t2
            ) x
            pivot 
            (
                min(changevalue)
                for entitychanged in (' + @cols + ')
            ) p '

execute(@query)

Both will give you the same result.