Split columns into rows based on data type-CodePudding

I have a table like this

 parent_id       col1        col2       col3
     101          John         1       9/12/2009 4:33:22 PM
     102          Fid          2       2/10/2005 12:54:01 PM
     103          Smith        3       6/4/2005 10:10:11 PM

col1 is char
col2 is int col1 is timestamp

And would like to create a second table based on the first table with sample output like below

 parent_id   ColName       Charvalue    IntValue  TimeValue
     101     Col1            John         null      null
     101     Col1            Fid          null      null       
     101     Col1            Smith        null      null              
     102     Col2            null          1        null       
     102     Col2            null          2        null       
     102     Col2            null          3        null       
     103     Col3            null         null      9/12/2009 4:33:22 PM
     103     Col3            null         null      2/10/2005 12:54:01 PM
     103     Col3            null         null      6/4/2005 10:10:11 PM

Should I use unpivot to achieve in MS SQL Server?

CodePudding user response：

You can try to use UNION ALL instead of unpivot

SELECT 101 parent_id ,col1,NULL col2,NULL col3 
FROM T
UNION ALL
SELECT 102,NULL,col2 ,NULL  
FROM T
UNION ALL
SELECT 103,NULL ,NULL,col3
FROM T

CodePudding user response：

You can unpivot using CROSS APPLY (VALUES, this only requires a single scan of the base table so is very efficient

SELECT
  t.parentid,
  v.ColName,
  v.Charvalue,
  v.IntValue,
  v.TimeValue
FROM YourTable t
CROSS APPLY (VALUES
    ('col1', t.col1, NULL, NULL),
    ('col2', NULL, t.col2, NULL),
    ('col3', NULL, NULL, t.col3)
) v(ColName, Charvalue, IntValue, TimeValue);