Unpivot and Pivot Sample Code



Create table #test (id INT,Column1 NVARCHAR(10),Column2 NVARCHAR(10),Column3 NVARCHAR(10))
INSERT INTO #test values( 1,'A',NULL,'B'),(2,'X','Y','Z'),(3,'P','Q',null),(4,null,'W','U') 


Select id, ISNULL('Column1'+'|'+[Column1],'') + ISNULL('Column2'+'|'+[Column2],'') + ISNULL('Column3'+'|'+[Column3],'')  as ValueData 
FROM (
Select id,col,val from #test
Cross Apply (Values(Column1,'Column1'),(Column2,'Column2'),(Column3,'Column3')) d(val,col)
WHere Val Is not null
) src
Pivot (max(val) For col In ([Column1],[Column2],[Column3])) pvt

--The cleanup can be removed when implemented inside a SP
IF OBJECT_ID('tempdb..#test') IS NOT NULL 
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