locked
Using Dynamic SQL to concatenate two columns into a table header with their respective values RRS feed

  • Question

  • I have a two tables, GuestList and CustomerList. I joined them and used dynamic SQL pivot table to convert the 'city' column from GuestList table into rows or table headers and the average population would be displayed under each city. So after executing the query at the bottom, my table header looks like this and the average population is displayed under each city.


    Time| Atlanta| Los Angeles | New York | Denver| Minneapolis


    But I want my table header to look like this. Basically 'Id' has four values, 1, 2,3,4 and each city has all these four ID's. I could not add all the cities but rest of the cities will also be like this.


    Time| Atlanta_1|| Atlanta_2|| Atlanta_3|| Atlanta_4|


    Could someone help me on this by writing the rest of the query on how to concatenate the two columns in the GuestList table and put their respective population underneath it.

    declare @ColumnNames nvarchar(max) = ''
    declare @SQL nvarchar(max) = ''
    
    select @ColumnNames += QUOTENAME(a.address) + ','
    from GuestList as a
    inner join CustomerList as b
    on a.Id = b.Id
    group by a.address
    order by a.address 
    
    set @ColumnNames = left(@ColumnNames, LEN(@ColumnNames)-1  )
    
    set @SQL= N'
    select Time,' + @ColumnNames + '
    from 
    (
    select a.Time, a.city, a.population, b.Gender
    from GuestList as a
    inner join CustomerList as b
    on a.Id = b.Id
    inner join Split(@city, '','') as c
    on a.city = c.Data
    where a.city = c.Data
    ) as SourceTable
    pivot
    (avg(population) 
    for city 
    in (' 
        + @ColumnNames + 
        ')) as PivotTable
    order by Time'
    
    
    execute sp_executesql @SQL,
                            N'@city nvarchar(max)'
                            ,@city = 'Atlanta,Los Angeles,New York'
    Wednesday, July 11, 2018 9:24 PM

Answers

  • Try this in your query for @ColumnNames

    select @ColumnNames += QUOTENAME(a.address+'_'+Convert(Varchar(10),a.Id )) + ','
    from GuestList as a
    inner join CustomerList as b
    on a.Id = b.Id
    group by a.address
    order by a.address 

    And in the  source for pivot  edit the city like this


    select a.Time, a.city+'_'+Convert(Varchar(10),a.Id ) as City, a.population, b.Gender
    from GuestList as a
    inner join CustomerList as b
    on a.Id = b.Id
    inner join Split(@city, '','') as c
    on a.city = c.Data
    where a.city = c.Data
    ) as SourceTable


    mohammad waheed


    • Edited by Mohammad Waheed Wednesday, July 11, 2018 9:46 PM
    • Marked as answer by tkhan17 Thursday, July 12, 2018 3:47 PM
    Wednesday, July 11, 2018 9:35 PM

All replies

  • Try this in your query for @ColumnNames

    select @ColumnNames += QUOTENAME(a.address+'_'+Convert(Varchar(10),a.Id )) + ','
    from GuestList as a
    inner join CustomerList as b
    on a.Id = b.Id
    group by a.address
    order by a.address 

    And in the  source for pivot  edit the city like this


    select a.Time, a.city+'_'+Convert(Varchar(10),a.Id ) as City, a.population, b.Gender
    from GuestList as a
    inner join CustomerList as b
    on a.Id = b.Id
    inner join Split(@city, '','') as c
    on a.city = c.Data
    where a.city = c.Data
    ) as SourceTable


    mohammad waheed


    • Edited by Mohammad Waheed Wednesday, July 11, 2018 9:46 PM
    • Marked as answer by tkhan17 Thursday, July 12, 2018 3:47 PM
    Wednesday, July 11, 2018 9:35 PM
  • Hi tkhan17,

    Welcome to Transact-SQL community forum.

    In any case, for this type of question, the best is to post:

    1) CREATE TABLE statements for the table(s) involved. 
    2) INSERT statements with sample data.
    3) The desired result given the sample.
    4) A short description of the business rules to explain why you want that result.
    5) Which version of SQL Server you are using.

    But in your case, I have no table structure and sample data of the table "GuestList","CustomerList" that you're using. Therefore, it is hard to help you solve your problem.

    Please provide these information to support your problem.

    Thanks for your understanding and support.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 12, 2018 2:27 AM