Search code examples
sqlsql-server

Use value from one SQL query as column name in another SQL query

I am searching for a way to display the values of one SQL query as column names in another SQL statement. I see many examples online talking about using a pivot or crosstab. I tried both but can't really find my way out. If somebody can give me a hint in the right direction with the following:

enter image description here

As you can see I want to add the "Kledingmaat" as title instead of usr1 and so on.

More info: I have a table called personen, this table has 10 columns USR1, USR2, USR3, USR4, USR5, USR6, USR7, USR8, USR9, USR10. Then I have a table called uservelden which has the following columns:

VOLGNR, USERTABELNAAM, CAPTIONSTRING

How can I achieve the following result:

enter image description here

My SQL:

WITH ColumnNames AS 
(
    SELECT TOP 10 CAPTIONSTRING, VOLGNR
    FROM USERVELDEN
    WHERE (USERVELDNAAM LIKE 'USR[1-9]' OR USERVELDNAAM LIKE 'USR[1-9][0-9]')
      AND ACCOUNT_ID = 258
      AND USERTABELNAAM = 'PERSONEN'
    ORDER BY VOLGNR
)
SELECT
    MAX(CASE WHEN ColumnNames.VOLGNR = 1 THEN usr1 END) AS 'Kledingmaat',
    MAX(CASE WHEN ColumnNames.VOLGNR = 2 THEN usr2 END) AS 'Kledingmaat anders',
    MAX(CASE WHEN ColumnNames.VOLGNR = 3 THEN usr3 END) AS 'Dieetwensen',
    MAX(CASE WHEN ColumnNames.VOLGNR = 4 THEN usr4 END) AS 'Shoe size',
    MAX(CASE WHEN ColumnNames.VOLGNR = 5 THEN usr5 END) AS 'Fifth',
    MAX(CASE WHEN ColumnNames.VOLGNR = 6 THEN usr6 END) AS 'Sixth',
    MAX(CASE WHEN ColumnNames.VOLGNR = 7 THEN usr7 END) AS 'Seventh',
    MAX(CASE WHEN ColumnNames.VOLGNR = 8 THEN usr8 END) AS 'Eighth',
    MAX(CASE WHEN ColumnNames.VOLGNR = 9 THEN usr9 END) AS 'Ninth',
    MAX(CASE WHEN ColumnNames.VOLGNR = 10 THEN usr10 END) AS 'Tenth'
FROM 
    PERSONEN
CROSS JOIN 
    ColumnNames
WHERE 
    ACCOUNT_ID = 258
    AND INACTIEF = 'F';
Solution

  • Using ideas from SQL Server dynamic PIVOT query? I was able to construct this SQL-Server dynamic column solution.

    This should build @cols = 'usr1 as [Kledinmaat], usr2 as [Kledinmaat anders], usr3 as [Dieetwensen], usr4 as [Shoe size]'

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

SET @cols = STUFF((SELECT distinct ',usr' + CAST(Row_Number() OVER (ORDER BY (SELECT NULL))  as Varchar(3)) + ' as '+ QUOTENAME(c.CAPTIONSTRING) 
            FROM uservelden c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SELECT @cols

set @query = 'SELECT ' +  @cols + ' FROM personen '

execute(@query)

    fiddle

    Kledinmaat Kledinmaat anders Dieetwensen Shoe size
    null null null null
    null XXX... null null
    null null null null
    null XA... null null
    null null null null
    708 null null null
    708 null null null
    null null null null
    710 null null null
    null XS null null
    708 null null null
    708 null null null