0

I am trying to insert dynamically generated column headers into a pivot statement without using stored procs due to how output is used with BI case tool. The first pivot statement below works but is hard-coded, the second uses sql code to generate the same output verbatim as a single text field but results in this error:

Msg 156, Level 15, State 1, Line 53 Incorrect syntax near the keyword 'SELECT'.

Msg 102, Level 15, State 1, Line 56 Incorrect syntax near ')'.

have searched in vain for awhile, any help greatly appreciated.

--Hard Coded Column Headers:

PIVOT (MAX(Result) FOR ColHdr IN ( 

  [Column A], [Column B], [Column C], [Column D]

                                  )) AS P

--Dynamically Generated Column Headers

PIVOT (MAX(Result) FOR ColHdr IN (

  SELECT TOP 1 substring( (SELECT ', [' + [FriendlyName] + ']' FROM [dbo].
  [TestResultSet] d FOR XML PATH ('')), 2, 1000) AS [ColHdrList] FROM [dbo].
  [TestResultSet]

                                  )) AS P
Rich
  • 1
  • 2
  • What version of SQL? If it's SQL Server 2008+ you can use STUFF. [Something like this example](https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/) – Jacob H Aug 11 '17 at 13:05
  • Even with STUFF, I'm pretty sure you can't do what you're attempting without multi-step dynamic sql. – Tab Alleman Aug 11 '17 at 13:21
  • duplicate: https://stackoverflow.com/questions/13245364/ms-sql-server-pivot-table-with-subquery-in-column-clause – Tab Alleman Aug 11 '17 at 13:31
  • You don't have to use a stored procedure, but you *have* to use dynamic SQL (such as [described here](https://stackoverflow.com/questions/10404348/)). One query, one result set, that's how SQL works. (Except for `SELECT *`, but we don't talk about that.) If your BI tool does not allow multi-statement queries, you are out of luck. – Jeroen Mostert Aug 11 '17 at 13:32
  • Ultimately, you're going to produce a table whose columns are different from time to time. How does this integrate with your 'BI tool'? Anyway, if you're using SSIS then you may want to investigate BIML from Varigence. This will dynamically generate a package -- which in turn can run with all the performance improvements available to explicit code. – Ryan B. Aug 11 '17 at 13:54

0 Answers0