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

user_16263328 0
Viewed 181

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?

ssms sql-server sql
3 Answers

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
user_15700000

If you don't want to mention column names , here is a more dynamic way:

SELECT t.id, t.RandomInformation, b.[Key] AS data 
FROM tablename t 
CROSS APPLY (SELECT * 
             FROM OPENJSON((SELECT t.* FROM tablename FOR JSON PATH,WITHOUT_ARRAY_WRAPPER))) b 
WHERE b.[Key] NOT IN ('ID', 'RandomInformation')

inspired by John Cappelletti

user_13674540

In SQL Server, you can also use UNPIVOT to do this. ie:

DECLARE @myTable TABLE
  (
    ID                INT
  , Data1             VARCHAR(50)
  , Data2             VARCHAR(50)
  , Data3             VARCHAR(50)
  , Data4             VARCHAR(50)
  , Data5             VARCHAR(50)
  , Data6             VARCHAR(50)
  , RandomInformation VARCHAR(50)
  );

INSERT INTO @myTable(ID, Data1, Data2, Data3, Data4, Data5, Data6, RandomInformation)
VALUES(1, 'Test1', NULL, 'Test2', NULL, 'Test3',NULL, 'Information1')
    , (2, NULL, 'Test5', NULL, 'Test6', NULL, 'Test7', 'Information2')
    , (3, 'Test8', 'Test9', NULL, NULL, 'Test10', 'Test11', 'Information3');

SELECT d.ID, d.x, d.RandomInformation, d.Data 
FROM
(SELECT ID, Data1, Data2, Data3, Data4, Data5, Data6, RandomInformation FROM @myTable) t 
UNPIVOT
(
   [Data] FOR x IN (Data1, Data2, Data3, Data4, Data5, Data6)
) AS d;
user_8949770
Related