Conversion of column based record to row based record in SQL Server

Question

I have the following table which contains 5 columns for instance (The actual table consists of like 100 columns).

Table name: SuperStore (source)

Columns:

rid (PK), OrderID(nvarchar), OrderDate(date), Address(nvarchar), CustomerName(nvarchar), Category(nvarchar)

Sample data:

| rid  | OrderID| OrderDate   || Address| CustomerName| Category|
|1     |00100100| 12/12/2009  || 1233   | center      | right   |
|2     |00100111| 12/12/2009  || 1234   | center      | left    |

I have some tens of thousand's records in this.

I need the following format to be inserted in another table

Table name: Allrecords (destination)

Columns:

rowid(PK), rid(FK), TableName, ColumnName, ColumnValue, CreatedDate, ModifiedDate

Sample data:

 | rowid| rid| TableName || ColumnName| ColumnValue | CreatedDate|ModifiedDate |
 |1     |1   | SuperStore|| OrderID     | 00100100  |            |
 |2     |1   | SuperStore|| OrderDate   | 12/12/2009|            |
 |3     |1   | SuperStore|| Address     | 1233      |            |

Of course I can do this through a c# program that will read the source record and insert into allrecord, but I want to do this through stored procedures and also I have many source tables having many columns and need to insert into the allrecord table. Is there a way to identify the source table columns dynamically through a stored procedure and insert into the allrecords table?

Answer 1

You can use UNPIVOT for this, the only tricky part is making sure all three columns are converted to the exact same data type (I'm guessing OrderID can't be > 500 characters, though the fact it's Unicode in the first place is surprising, and guessing also that Address is limited to something less than 500 characters):

;;; /* for safety */ ;;;WITH src AS
(
  SELECT rid, 
    TableName = N'SuperStore', 
    OrderID   = CONVERT(nvarchar(500), OrderID),
    OrderDate = CONVERT(nvarchar(500), OrderDate, 101), 
    -- the 101 format is a guess? avoid ambiguous formats
    Address   = CONVERT(nvarchar(500), Address)
  FROM dbo.SuperStore
)
SELECT 
  [rowid] = rid, -- (assume this is generated in the target IDENTITY col?)
  rid, 
  TableName, 
  ColumnName, 
  ColumnValue
FROM src
UNPIVOT 
(
  ColumnValue FOR ColumnName IN (OrderID, OrderDate, Address)
) AS up;

Output:

rowid	rid	TableName	ColumnName	ColumnValue
1	1	SuperStore	OrderID	00100100
1	1	SuperStore	OrderDate	12/12/2009
1	1	SuperStore	Address	1233
2	2	SuperStore	OrderID	00100111
2	2	SuperStore	OrderDate	12/12/2009
2	2	SuperStore	Address	1234

You didn't explain where the additional date columns came from, but they'd be easy to add either inside src or outside.

• Example db<>fiddle