Convert or transform rows into columns in sql server 2017


There are several ways that you can convert or transform data from multiple rows into columns in Sql Server 2017 using PIVOT or without using PIVOT.

For each Product there can be many predefined attributes like "Price","ProductImage","AlternateImage" etc. Each attribute is inserted as rows in ProductAttributeValues table. Now to get each attribute as column you can use any of the below solution.

Product Table
ProductId ProductName
1 Redmi Note 4
2 Samsung Metro 313 (Gold)
Attributes Table
AttributeIdAttributeNames
1Price
2ProductImage
3AlternateImage1
4AlternateImage2
5AlternateImage3
6AlternateImage4
7RAM
8Smartphone
9ScreenSize
10OperatingSystem
11SimType
12SecondaryCamera
ProductAttributeValues Table
ProductAttributeIdAttributeIdAttributeValueProductId
11119991
22ProductImage.jpg1
33Image1.jpg1
44Image2.jpg1
55Image3.jpg1
66Image4.jpg1
774 GB1
88Yes1
995.5 inch Full HD Display1
1010Android Marshmallow 6.0.11
1111Dual Sim1
13119902
142ProductImage.jpg2
153Image1.jpg2
164Image2.jpg2
175Image3.jpg2
186Image4.jpg2
1974 GB2
208No2
2192.03 inch2
2211Dual Sim2
12125MP1

Solution 1: Without PIVOT

Without using the PIVOT you can use an aggregate function with a CASE expression to Convert or transform rows into columns in sql server 2017.

Select #break MAX(Case When AttributeNames = 'Price' Then AttributeValue End) Price, #break MAX(Case When AttributeNames = 'ProductImage' Then AttributeValue End) ProductImage, #break MAX(Case When AttributeNames = 'AlternateImage1' Then AttributeValue End) AlternateImage1, #break MAX(Case When AttributeNames = 'AlternateImage2' Then AttributeValue End) AlternateImage2, #break MAX(Case When AttributeNames = 'AlternateImage3' Then AttributeValue End) AlternateImage3, #break MAX(Case When AttributeNames = 'AlternateImage4' Then AttributeValue End) AlternateImage4, #break MAX(Case When AttributeNames = 'RAM' Then AttributeValue End) RAM, #break MAX(Case When AttributeNames = 'Smartphone' Then AttributeValue End) Smartphone, #break MAX(Case When AttributeNames = 'ScreenSize' Then AttributeValue End) ScreenSize, #break MAX(Case When AttributeNames = 'OperatingSystem' Then AttributeValue End) RAM, #break MAX(Case When AttributeNames = 'SimType' Then AttributeValue End) SimType, #break MAX(Case When AttributeNames = 'SecondaryCamera' Then AttributeValue End) SecondaryCamera #break From ProductAttributeValues PA Left Outer Join Attributes A on PA.AttributeId=A.AttributeId Where PA.ProductId=1 #break;
Result:
Price ProductImage AlternateImage1 AlternateImage2 AlternateImage3 AlternateImage4 RAM Smartphone ScreenSize RAM SimType SecondaryCamera
11999 ProductImage.jpg Image1.jpg Image2.jpg Image3.jpg Image4.jpg 4 GB Yes 5.5 inch Full HD Display Android Marshmallow 6.0.1 Dual Sim 5MP

Solution 2: With PIVOT

Using the PIVOT you can Convert or transform rows into columns in sql server 2017.

Select Price,ProductImage,AlternateImage1,AlternateImage2,AlternateImage3,AlternateImage4,RAM, #break Smartphone,ScreenSize,OperatingSystem,SimType,SecondaryCamera #break From #break ( #break Select AttributeValue, A.AttributeNames #break From ProductAttributeValues PA Join Attributes A ON PA.AttributeId=A.AttributeId Where PA.ProductId=1 #break ) d #break Pivot #break ( MAX(AttributeValue) #break For AttributeNames IN (Price,ProductImage,AlternateImage1,AlternateImage2,AlternateImage3,AlternateImage4,RAM, #break Smartphone,ScreenSize,OperatingSystem,SimType,SecondaryCamera #break ) #break ) piv;
Result:
Price ProductImage AlternateImage1 AlternateImage2 AlternateImage3 AlternateImage4 RAM Smartphone ScreenSize RAM SimType SecondaryCamera
11999 ProductImage.jpg Image1.jpg Image2.jpg Image3.jpg Image4.jpg 4 GB Yes 5.5 inch Full HD Display Android Marshmallow 6.0.1 Dual Sim 5MP

Solution 3: Using Dynamic SQL

You can use Dynamic SQL if the number of ColumnNames are unknown to convert or transform rows into columns in sql server 2017.

Declare @Cols AS NVARCHAR(MAX) #break Declare @Query AS NVARCHAR(MAX) #break Select @Cols = STUFF((Select ',' + QUOTENAME(AttributeNames) #break From Attributes Group By AttributeNames, AttributeId #break Order By AttributeId #break For XML PATH(''), TYPE #break ).value('.', 'NVARCHAR(MAX)') #break ,1,1,'') #break Set @Query = N'Select ' + @Cols + N' From #break ( #break Select AttributeValue, A.AttributeNames #break From ProductAttributeValues PA Join Attributes A on PA.AttributeId=A.AttributeId #break Where PA.ProductId=1 #break ) x #break Pivot #break ( MAX(AttributeValue) For AttributeNames IN (' + @Cols + N') #break ) p' #break EXEC sp_executesql @Query;
Result:
Price ProductImage AlternateImage1 AlternateImage2 AlternateImage3 AlternateImage4 RAM Smartphone ScreenSize RAM SimType SecondaryCamera
11999 ProductImage.jpg Image1.jpg Image2.jpg Image3.jpg Image4.jpg 4 GB Yes 5.5 inch Full HD Display Android Marshmallow 6.0.1 Dual Sim 5MP

Download File
Scripts.sql

Share On