Pivot Table on Column Datatype in SSIS/SQL

Question

I have been tasked to transform the following table:

    +---------------+----------+---------+-------------+-----+-------------+--------+
    | AnnualRevenue |   City   | Company | CreatedDate | Id  | IsConverted | UserId |
    +---------------+----------+---------+-------------+-----+-------------+--------+
    | NULL          | New York | ABC     | 1/03/2015   | 123 |           0 | A1     |
    | 200           | NULL     | DEF     | 2/03/2016   | 456 |           1 | A1     |
    +---------------+----------+---------+-------------+-----+-------------+--------+

in either a SQL query or SSIS to this:

+-----+---------------+----------+-----------+------+------+--------+
| Id  |     name      | nvarchar |   date    | int  | bit  | UserId |
+-----+---------------+----------+-----------+------+------+--------+
| 123 | AnnualRevenue | NULL     | NULL      | NULL | NULL | A1     |
| 123 | City          | New York | NULL      | NULL | NULL | A1     |
| 123 | Company       | ABC      | NULL      | NULL | NULL | A1     |
| 123 | CreatedDate   | NULL     | 1/03/2015 | NULL | NULL | A1     |
| 123 | IsConverted   | NULL     | NULL      | NULL | 0    | A1     |
| 456 | AnnualRevenue | NULL     | NULL      | 200  |      | A1     |
| 456 | City          | NULL     | NULL      | NULL | NULL | A1     |
| 456 | Company       | DEF      | NULL      | NULL | NULL | A1     |
| 456 | CreatedDate   | NULL     | 2/03/2016 | NULL | NULL | A1     |
| 456 | IsConverted   | NULL     | NULL      | NULL | 1    | A1     |
+-----+---------------+----------+-----------+------+------+--------+

I've tried to research online and found PIVOT transform in SSIS but I've never used that before. I'm unable to figure out how I can achieve the desired outcome using it. Can anyone point me in the right direction?

Answer 1

This option will dynamically unpivot your data and link data type to the Information Schema.

I should note, that the XML field names ID and UserId are case sensitive

Example

Select A.ID
      ,A.Name
      ,[nvarchar] = case when data_type='nvarchar' then value end
      ,[date]     = case when data_type='date'     then value end
      ,[int]      = case when data_type='int'      then value end
      ,[bit]      = case when data_type='bit'      then value end
      ,A.UserID
 From (
        Select C.*
         From  YourTable A
         Cross Apply (Select XMLData = cast((Select A.* For XML Raw) as xml)) B
         Cross Apply (
                        Select Id     = r.value('@Id','int')
                              ,UserID = r.value('@UserId','varchar(25)')
                              ,Name   = attr.value('local-name(.)','nvarchar(100)')
                              ,Value  = attr.value('.','nvarchar(max)') 
                         From  B.XMLData.nodes('/row') as A(r)
                         Cross Apply A.r.nodes('./@*') AS B(attr)
                         Where attr.value('local-name(.)','varchar(100)') not in ('Id','UserId')
                     ) C
       ) A
 Join (Select Column_Name,Data_Type From INFORMATION_SCHEMA.COLUMNS Where Table_Name='YourTable') B on B.Column_Name=A.Name

Returns