Dynamic queries with exec
Question

I am trying to convert rows values to use as columns headers best explained with the following link,

https://data.stackexchange.com/stackoverflow/query/497433

I attached the data screenshot for your reference.

This is the custom query and i have set both the "cols" and "query" as input parameter with Text as data type and expand inline to true.

I kept getting error

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

select @cols = STUFF((
                        SELECT ',' + QUOTENAME({PersonAttributes}.[ColumnName])
                        FROM {PersonAttributes}
                        GROUP BY {PersonAttributes}.[ColumnName], {PersonAttributes}.[Id]
                        ORDER BY {PersonAttributes}.[Id]
                        FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'),1,1,''
                    )

set @query = N'SELECT ' + @cols + N' FROM
             (
                SELECT {PersonAttributes}.[Value], {PersonAttributes}.[ColumnName]
                FROM {PersonAttributes}
             ) x
             PIVOT
             (
                MAX(value)
                FOR {PersonAttributes}.[ColumnName] IN (' + @cols + N')
             ) p '

exec sp_executesql @query;

20191118001.png

I checked various threads, one of which is,

https://www.outsystems.com/forums/discussion/3298/declaring-and-using-sql-variables-in-an-advanced-query/

I am still getting error though.


Did i wrote the query wrongly?

did you find the answer?

Hi EnthusiaticNewbie,

You've got to give 'cols' and 'query' the value "@cols" and "@query", set expand inline to true.
Then you should be able to use them as you already did.


Regards,
Lennart

If this doesn't work, you can of course also use 2 queries. One to get the list of columns and one to do the pivot.

You can let the first one return a list, then use Text.String_Join to create a comma-separated string and feed that to the second query as the columns to select.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.