Dynamic Column List In Sorting Order



create table KeywordFieldMap(fieldname varchar(50))
Insert into KeywordFieldMap values('aaa'),('bbbb'),('aaa'),('ab0s'),('1aaa'),('zaaa')
DECLARE @cols AS NVARCHAR(MAX);

select @cols = STUFF( (SELECT  ',' + QUOTENAME(fieldName)  FROM KeywordFieldMap  
Group by fieldName
Order by fieldName 
FOR XML PATH(''),type).value('.','varchar(max)'),1,1,'') 

select @cols



drop table KeywordFieldMap


--Another
create table test (Header  varchar(50), Score  varchar(50),  UserID int)
insert into test values('Grade','C',1)
,('Grade','B',2)
, ('Percent','79.80',1)
,('Percent','89.0',2)

DECLARE @col AS NVARCHAR(max)=''
DECLARE @sql AS NVARCHAR(max)=''
  
SELECT @col=stuff( (SELECT  distinct ',['+ Header  +']'
FROM test   
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;

SET @sql='SELECT [UserID], ' + @col + ' FROM (
Select userid,Header,Score from test) src
Pivot (max(Score) For Header in ('+ @col + ')) AS pvt'
 
 
EXEC sp_executesql @sql

 drop table test

 
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