Skip to content
Advertisement

Bring a row for each specific column that is not empty, with the column name

I’ve this table with the following data:

ID Data1 Data2 Data3 Data4 Data5 Data6 RandomInformation
1 Test1 Test2 Test3 Information1
2 Test5 Test6 Test7 Information2
3 Test8 Test9 Test10 Test11 Information3

The resultant table i want should be as below:

ID Data RandomInformation
1 Data1 Information1
1 Data3 Information1
1 Data5 Information1
2 Data2 Information2
2 Data4 Information2
2 Data6 Information2
3 Data1 Information3
3 Data2 Information3
3 Data5 Information3
3 Data6 Information3

Can someone please help me out with this query?

Advertisement

Answer

You can use a CROSS APPLY in concert with VALUES to UNPIVOT your data

Select A.ID
      ,B.Data
      ,A.RandomInformation
 From  YourTable A
 Cross Apply ( values ('Data1',Data1)
                     ,('Data2',Data2)
                     ,('Data3',Data3)
                     ,('Data4',Data4)
                     ,('Data5',Data5)
                     ,('Data6',Data6)
             ) B(Data,Value)
 Where B.Value is not null
5 People found this is helpful
Advertisement