sql server - Sqlserver PIVOT to turn a "reconstruct" a flat table into columns - why does this not work? -

the system using allows data entry form created multiple user defined fields satisfy information required on particular group of different "ordes". fields stored in database such entered:

guid orderguid userdatacode  value 1    100       ordername     breakfast 2    100       orderdesc     food eat before lunch 3    100       cerealyn      y 4    100       toastyn       y 5    100       toastdesc     white bread 6    100       paperyn       y 7    100       paperdesc     newsroom 8    101       ordername     lunch 9    101       orderdesc     food eat before dinner 10   101       cerealyn      n 11   101       toastyn       y 12   101       toastdesc     brown bread 13   101       paperyn       y 14   101       paperdesc     middaynews (etc) 

(in fact enterprise hospital software have used simpler examples here)

i using sql return table pivoted below

orderguid   ordername   orderdesc   cerealyn   toastyn   toastdesc    .... 101         breakfast   food you..  y          y         white bread  .... 102         lunch       food you..  n          y         brown bread  .... 

i wrote following sql based on examples found on net:

declare @dynamicpivotquery nvarchar(max) declare @columnname nvarchar(max)  --get distinct values of pivot column   select @columnname= isnull(@columnname + ',','')         + quotename([userdatacode]) (     select           [userdatacode]              [xxx].[dbo].[cv3orderuserdata]     orderguid = 3000680     ) codes;  --prepare pivot query using dynamic  set @dynamicpivotquery = n'select orderguid, ' + @columnname + '             [xxx].[dbo].[cv3orderuserdata]     pivot(max(value)            userdatacode in (' + @columnname + ')) pvttable' --execute dynamic pivot query  --select @dynamicpivotquery  exec sp_executesql @dynamicpivotquery 

however while pivot requested.. , puts values in correct new "dynamic" columns, if returns row each orderguid + value, ie:

orderguid   ordername    orderdesc   cerealyn   toastyn 100         breakfast    null        null       null  ... 100         null         food you..  null       null  ... 101         null         null        y          null  ... 

etc.etc

what doing wrong :( ?

the problem in query pivot source query has guid column makes pivot operator consider guid column.

to expected output need remove guid column pivot source query.

here static version can convert dynamic version did.

select * ( select orderguid,userdatacode,value             tst)     pivot(max(value)            userdatacode in ([ordername],[orderdesc],                                [cerealyn],[toastyn],                               [toastdesc],[paperyn],                               [paperdesc])) pvttable 

• sqlfiddle demo