Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Transposing a table from multicolumn to key-value Expand / Collapse
Posted Friday, June 7, 2013 4:56 AM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 8, 2015 11:18 PM
Points: 346, Visits: 524
I have a transactional table with 100+ columns and each row is uniquely identified by an id column. For a data export requirement, we need to make export the table flat - aka, id/column_name/column_value format. the number of columns are dynamic in nature. By this, I mean : there could be 10 additional columns in near future. Some columns might get dropped as well. This is another reason for exporting as a name/value pair.

A dynamic pivoting might be the answer - I tried using dynamic pivoting using xmlpath/cross apply and made things complex enough to get a cross joined resultset. Here is the sample schema (input/output) with sample data:!3/f0efa/7

Any help is highly appreciated.

Post #1461027
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse