none
Concatenating a column's value from different rows in Azure SQL Dw RRS feed

  • Question

  • HI,

    I need to concatenate all the department names in to a string. This is as part of  a larger requirement to have a PIVOT but with dynamic built department names. (please see here :

    https://stackoverflow.com/questions/42292480/dynamic-pivot-query-how-to-save-it-in-sql-server

    Please note new departments could be added into the table all the time. 

    For for getting the names into a string, I have the following :

     

    DECLARE @ColumnName AS VARCHAR(1000)

    Select  @ColumnName=COALESCE (Rtrim(@ColumnName + ',[' + [Names] + ']'), '['+[Names]+']')
                   FROM    (SELECT DISTINCT [Names] from [dbo].[Depttable]) Department
    select @ColumnName

    This works well in standard SQL Server but on DW i get the following error:

    Msg 104473, Level 16, State 1, Line 115
    A variable that has been assigned in a SELECT statement cannot be included in a expression or assignment when used in conjunction with a from clause.

    How can i achieve this in Azure SQL DW?

    Thanks

    MS

    Thursday, October 5, 2017 6:52 AM

Answers

  • Here is a version that may work for you.

    CREATE TABLE #NAMES 
    WITH (DISTRIBUTION = ROUND_ROBIN)
    AS
    SELECT ROW_NUMBER() OVER (ORDER BY Names) AS ROWNUM, Names
     from (SELECT DISTINCT [Names] from [dbo].[Depttable]
     )A
    DECLARE @total int = (select count(*) from #NAMES)
    DECLARE @i int = 1
    DECLARE @Concat AS VARCHAR(1000)= ''
    WHILE (@i <= @total)  
    BEGIN
    DECLARE @ColumnName VARCHAR(200) = (select Names from #NAMES where ROWNUM =@i)
    SET @Concat += '[' + @ColumnName + '],';
    SET @i+=1
    END
    SELECT @Concat

    Thursday, December 21, 2017 10:24 PM

All replies

  • Hi,

    did you get any solution to this? I am exactly looking for the same.

    Hrushi

    Friday, December 15, 2017 11:37 AM
  • Here is a version that may work for you.

    CREATE TABLE #NAMES 
    WITH (DISTRIBUTION = ROUND_ROBIN)
    AS
    SELECT ROW_NUMBER() OVER (ORDER BY Names) AS ROWNUM, Names
     from (SELECT DISTINCT [Names] from [dbo].[Depttable]
     )A
    DECLARE @total int = (select count(*) from #NAMES)
    DECLARE @i int = 1
    DECLARE @Concat AS VARCHAR(1000)= ''
    WHILE (@i <= @total)  
    BEGIN
    DECLARE @ColumnName VARCHAR(200) = (select Names from #NAMES where ROWNUM =@i)
    SET @Concat += '[' + @ColumnName + '],';
    SET @i+=1
    END
    SELECT @Concat

    Thursday, December 21, 2017 10:24 PM
  • Thanks,

    It helped me.

    Friday, April 12, 2019 9:12 AM
  • PERFECT !!! THANKS A LOT!!

    VT

    Thursday, July 4, 2019 11:43 AM
  • Just a tip for you guys, in the latest release of Azure DW they added STRING_AGG which does this for you. https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=azure-sqldw-latest
    Friday, July 12, 2019 7:22 AM