1

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.

Using PIVOT will work here. However, the number of columns are very dynamic nature. By this, I mean : there could be 10 additional columns in near future. Some columns might get dropped. This is another reason for exporting as a name/value pair.

I checked the possiblity of using XML PATH as mentioned in here.

However, the issue is to associate the key/value pair to the id of the initial table. That is, the final table should look like this (the SQL Fiddle of an existing schema is here):

select * from finaltable;

idLoop|key|value

38215|DCC_Avg|29.29 

38215|DCC_StdDev|0.762

38215|DCC_Count|13.00 

..

..

38221|DCC_Avg|0

38221|DCC_StdDev|0

38221|DCC_Count|9.4

etc.

Any help is highly appreciated

Community
  • 1
  • 1
sabyasm
  • 56
  • 3
  • 1
    There are many questions already about [dynamic pivots](http://stackoverflow.com/search?q=sql+dynamic+pivot); you may get a better response if you show what you've tried so far. – Pondlife Jun 07 '13 at 19:47

0 Answers0