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;

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?

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.