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?

Obviously rowid cannot be "PK" if it repeats for every row associated with the same rid value. — SMor
– SMor, Commented Apr 2, 2022 at 12:19
To do this dynamically, see this article, though you'd have to have a way to determine what columns in any given table are the ones you need to unpivot, and what columns you don't... — Aaron Bertrand
– Aaron Bertrand, Commented Apr 2, 2022 at 12:24

Aaron Bertrand · Accepted Answer · 2022-04-02 12:25:40Z

2

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

edited Apr 2, 2022 at 12:25

answered Apr 2, 2022 at 12:18

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Venkat Over a year ago

I have numerous columns, probably 200 such columns, I want everything to be normalised. Is there a procedure where it could take the column names dynamically from the table names using master table? I understand you have given me a solution that used all the column names, but is there a way to identify the column names dynamically without explicitly stating them?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related