CODEWITHSUNDEEP
sqlsql-serverdynamicpivot
davids
davids

Reputation: 5577

How do I PIVOT in SQL Server with dynamic columns, values, and datatypes?

I have a table that stores the data as follows:

ID       ListId   ModuleId  RecordId    NValue    CValue  LValue  Caption  RowId
8cb5f604 c07cea6f 24cb218c  _01F15T1LJ  2.00000           False   Wire #   24cb218c
c023fe9e 2dcd56fe 24cb218c  _01F15T1LJ            Red     False   Label    24cb218c
4a74bd46 c07cea6f 24cb218c  _01F15T1LJ  1.00000           False   Wire #   24cb218d
30cae2cf 2dcd56fe 24cb218c  _01F15T1LJ            White   False   Label    24cb218d

I need to pivot the date on the RowId and display the results something like this:

RowId     Wire #   Label
24cb218d  1.00000  White
24cb218c  2.00000  Red

I cannot figure out how to force PIVOT to work in this way.

The data in the tables, number of columns, and order are all open for editing by the end user.  Is it possible to do this efficiently?  If so, how.

Upvotes: 2

Views: 4041

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300529

You can achieve this using dynamic TSQL (remember to use QUOTENAME to avoid SQL injection attacks):

[Obligatory reference to The Curse and Blessings of Dynamic SQL]

Upvotes: 2

Related Questions