PIVOT and TEMP TABLE

Question

please can you help me put this script into a temp table. i have been working on this for hours and it keeps giving me errors.

    DECLARE @PivotColumnHeaders VARCHAR(MAX)
    SELECT @PivotColumnHeaders = 
    COALESCE(@PivotColumnHeaders + ',[' + CAST(expansion as varchar(max)) + ']',
    '[' + CAST(expansio

    n as varchar(max))+ ']')

INTO #temp
FROM (Select distinct expansion from #CD4_VL2) results


DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
select * from
(
select subjectID,expansion,printableValue1
from #CD4_VL2) as results


PIVOT

(MAX([printableValue1])
FOR [expansion] IN (
' + @PivotColumnHeaders + '
)
) as PivotTable

--order by Performed_Date_And_Time desc
'

Execute(@PivotTableSQL )

What errors? If it's anything about syntax, have you *tried* `Print`ing the `@PivotTableSQL` instead of `Execute`ing it, and then examining the code? — Damien_The_Unbeliever, Apr 10 '14 at 13:48
Also (although I doubt this is the issue) you should be aware of [this warning](http://msdn.microsoft.com/en-us/library/ms189484.aspx): "Do not use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. This is because all expressions in the SELECT list (including assignments) are not guaranteed to be executed exactly once for each output row" — Damien_The_Unbeliever, Apr 10 '14 at 13:57
As per Damien's comment an alternative to using a mutating COALESCE to do `GROUP_CONCAT` is to use `STUFF` with xml, e.g. here http://stackoverflow.com/a/11985946/314291 — StuartLC, Apr 11 '14 at 08:20

score 0 · Answer 1 · answered Apr 10 '14 at 14:41

Because you've pivoted on the expansion columns, the respective values of MAX(printableValue1) will now be in columns with the names of the data in the Expansion column. Change this like so:

DECLARE @PivotColumnHeaders NVARCHAR(MAX) = 'Expansion1, Expansion2, Expansion3';

DECLARE @PivotTableSQL NVARCHAR(MAX);
SET @PivotTableSQL = 
N'select subjectID, ' + @PivotColumnHeaders 
+' from #CD4_VL2 
  PIVOT
(
  MAX([printableValue1])
  FOR [expansion] IN (' + @PivotColumnHeaders + ')) x';

Execute(@PivotTableSQL);

SqlFiddle here

PIVOT and TEMP TABLE

1 Answers1