Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
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: Friday, April 11, 2014 7:30 AM
Points: 346, Visits: 518
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:


http://sqlfiddle.com/#!3/f0efa/7


Any help is highly appreciated.




--
Sabya
Post #1461027
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse