-1

I want to keep the column names generated by @columnsToPivot intact when building my dynamic SQL query. Below is my @columnsToPivot query

DECLARE @columnsToPivot AS NVARCHAR(MAX);
DECLARE @pivotQuery AS NVARCHAR(MAX);

SELECT @columnsToPivot = STRING_AGG(QUOTENAME([QuestionaireId]) + ', ' + QUOTENAME('Response'), ',')
FROM (
    SELECT DISTINCT [QuestionaireId]
    FROM [prod].[CCRS_Audit]
) AS QIDS;

The output of the above query is the modified columns names

[23]Response, [43]Response, [56]Response

When I am using the above query to generate the dynamic query, Its removing the QuestionaireId's and only keeping Response as the column name. Below is the query

SET @pivotQuery = '
SELECT CaseId,
       Auditor,
       FormName,
       [Organization],
       [Accession Number],
       ' + @columnsToPivot + '
FROM (
    SELECT CaseId,
           Auditor,
           FormName,
           [Organization],
           [AccessionNumber],
           [QuestionaireId],
           [Response]
    FROM [prod].[CCRS_Audit]
) AS SourceTable
PIVOT (
    MAX(Response) FOR QuestionaireId IN (' + @columnsToPivot + ')
) AS PivotTable';
';

EXEC sp_executesql @pivotQuery;

The output of the above query is

Response, Response, Response

But I want the output should be in the below format

23Response, 43Response, 53Response
Shadow
  • 33,525
  • 10
  • 51
  • 64
merkle
  • 1,585
  • 4
  • 18
  • 33
  • Guess what? MySQL is a totally different product to SQL Server - do yourself a favour and correct your tags so the right experts look at your post. – Dale K Aug 23 '23 at 06:53
  • 1
    Assuming SQL Server, because this `[23]Response` is the same as `[23] AS Response` i.e. the []'s identify the column then the next text becomes an alias. Surely you want `[23Response]`? – Dale K Aug 23 '23 at 06:54
  • 2
    So `QUOTENAME(CONCAT(QuestionaireId, ', Response'))` - though you also need to append that string to the data if you want the `PIVOT` to match it – Martin Smith Aug 23 '23 at 07:04
  • @DaleK I want 23Response as Column name but not [23Response] – merkle Aug 23 '23 at 07:38
  • @MartinSmith Can you please tell on how to append that string to the data in the PIVOT – merkle Aug 23 '23 at 07:44
  • 1
    I know you do, the brackets don't become part of your column name, they are just delimiters. See [this](https://stackoverflow.com/questions/52898/what-is-the-use-of-the-square-brackets-in-sql-statements) or many other references. And they are mandatory when starting a column name/alias with a number. – Dale K Aug 23 '23 at 07:45

0 Answers0