A Dynamic PIVOT Sample–T-SQL


Here is another sample I may need later:


If object_id('table1') is not null
drop table table1

create table table1 (name varchar(50),	Detail varchar(50), DetailValue int)
insert into table1 Values('A','X',10),('A','Y',20),('A','Z',11),
('B','X',13),('B','Y',19),('B','Z',5),
('C','X',11),('C','Y',15),('C','Z',5)


DECLARE @col AS VARCHAR(max)=''
DECLARE @sql AS VARCHAR(max)

SELECT @col=stuff( (SELECT ',['+ Detail  +']'
FROM table1 p2
WHERE p2.name = p1.name
ORDER BY name, Detail
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')
FROM table1 p1
GROUP BY name ;

--print @col

SET @sql='SELECT [name], ' + @col
+ ' FROM table1 PIVOT (MAX(DetailValue) FOR [Detail] IN ('+ @col + ')) AS pvt'
--print @sql
EXEC (@sql)


Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s