Transposing a table from multicolumn to key-value

  • 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.

    [font="Comic Sans MS"]--
    Sabya[/font]

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply