none
How to return value from multiple rows in a single string

    Question

  •  

    I have a table having Style Nos (VarChar Col), how I can return values from multiple rows in a single string.

     

    for Example if table is having 3 records :-

     

    1. Style 1

    2. Style 2

    3. Style 3

     

    It should return single value in this way

     

    Style 1, Style 2, Style 3

     

     

    Sunday, October 07, 2007 3:22 PM

Answers

  • Concatenating row values in Transact-SQL

    http://www.projectdmx.com/tsql/rowconcatenate.aspx

     

     

    AMB

    Sunday, October 07, 2007 3:24 PM
    Moderator
  • Hi Vinodonly,

     

    I wonder why you did not use the one that uses "FOR XML PATH('')". It is simpler than the recursive one and faster.

     

    Code Block

    SELECT

    CategoryID,

    STUFF(

    (

    SELECT ',' + ProductName

    FROM dbo.[Products] AS p

    WHERE p.CategoryID = c.CategoryID

    ORDER BY ProductName

    FOR XML PATH('')

    ), 1, 1, '') AS product_list

    FROM

    dbo.[Categories] AS c;

     

     

     

    Anyway, here is how the recursive one works, but let us work with just one CategoryID, so you can see it better. Please execute this statement.

     

    WITH CTE ( CategoryId, product_list, product_name, length )

    AS

    (

    SELECT

    CategoryId,

    CAST('' AS VARCHAR(8000)),

    CAST('' AS VARCHAR(8000)),

    0

    FROM

    Northwind..Products

    WHERE

    [CategoryId] = 1

    GROUP BY

    CategoryId

    UNION ALL

    SELECT

    p.CategoryId,

    CAST( product_list + CASE WHEN length = 0 THEN '' ELSE ', ' END + ProductName AS VARCHAR(8000) ),

    CAST( ProductName AS VARCHAR(8000)),

    length + 1

    FROM

    CTE c

    INNER JOIN

    Northwind..Products p

    ON c.CategoryId = p.CategoryId

    WHERE

    p.ProductName > c.product_name

    )

    SELECT

    CategoryId, product_list, [length], [rank]

    FROM

    (

    SELECT

    CategoryId, product_list, [length],

    RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC)

    FROM

    CTE

    ) D ( CategoryId, product_list, [length], rank )

    ORDER BY

    [length], [rank] DESC

     

    First, the anchor part is pulling distinct [CategoryID] from table dbo.Products and two empty string columns, product_list and product_name, and 0 as lenght. Well, I do not know why they choose [length] instead cycle or pass. If we work with CategoryID = 1, then you will have a result like this from the anchor part:

     

    CategoryId product_list product_name length
    1 0

     

    Now the recursive part bring all ProductName with same CategoryID and ProductName > product_name, in this case all products with same CategoryID and ProductName > ''. Then for each row in the result from the first pass of recursive part, it will concatenate the product_name to the product_list list column from the anchor part. The result of the first pass on the recursive part will be something like:

     

    CategoryId product_list product_name length
    1 Chai Chai 1
    1 Chang Chang 1
    1 Guaraná Fantástica Guaraná Fantástica 1
    1 Sasquatch Ale Sasquatch Ale 1
    1 Steeleye Stout Steeleye Stout 1
    1 Côte de Blaye Côte de Blaye 1
    1 Chartreuse verte Chartreuse verte 1
    1 Ipoh Coffee Ipoh Coffee 1
    1 Laughing Lumberjack Lager Laughing Lumberjack Lager 1
    1 Outback Lager Outback Lager 1
    1 Rhönbräu Klosterbier Rhönbräu Klosterbier 1
    1 Lakkalikööri Lakkalikööri 1

     

    From that result, pull again all products with same CategoryID and ProductName > product_list, but notice that it will be done for each row, so for the product_name = 'Chai', the list will be 'Chang','Guaraná Fantástica', ..., 'Lakkalikööri'. The result will be a string of the concatenation of the previous product_list plus the new product_name. The result of the second pass for product_name 'Chai' will be:

     

    CategoryId product_list product_name length
    1 Chai, Chang Chang 2
    1 Chai, Guaraná Fantástica Guaraná Fantástica 2
    1 Chai, Sasquatch Ale Sasquatch Ale 2
    1 Chai, Steeleye Stout Steeleye Stout 2
    1 Chai, Côte de Blaye Côte de Blaye 2
    1 Chai, Chartreuse verte Chartreuse verte 2
    1 Chai, Ipoh Coffee Ipoh Coffee 2
    1 Chai, Laughing Lumberjack Lager Laughing Lumberjack Lager 2
    1 Chai, Outback Lager Outback Lager 2
    1 Chai, Rhönbräu Klosterbier Rhönbräu Klosterbier 2
    1 Chai, Lakkalikööri Lakkalikööri 2

     

    The same for product_name 'Chang'.

     

    CategoryId product_list product_name length
    1 Chang, Guaraná Fantástica Guaraná Fantástica 2
    1 Chang, Sasquatch Ale Sasquatch Ale 2
    1 Chang, Steeleye Stout Steeleye Stout 2
    1 Chang, Côte de Blaye Côte de Blaye 2
    1 Chang, Chartreuse verte Chartreuse verte 2
    1 Chang, Ipoh Coffee Ipoh Coffee 2
    1 Chang, Laughing Lumberjack Lager Laughing Lumberjack Lager 2
    1 Chang, Outback Lager Outback Lager 2
    1 Chang, Rhönbräu Klosterbier Rhönbräu Klosterbier 2
    1 Chang, Lakkalikööri Lakkalikööri 2

     

    Notice that the number of rows for 'Chai' is greater than the one for 'Chang', this is because of the filter expression. In other words, because the list has to be sort by ProductName, each pass concatenate previous product_list with each one of the products matching ProductName > product_name, were product_name is the name of the product in the last list. At the end, you will have a a lot of rows but the only one you need is the one that include the concatenation of all products for that category, in other words the list with the greates number of products in its list and for each CategoryID. That row si the one with greates [length] or number of pass, and this is being calculated using the ranking function RANK, but you can use any of the ranking functions in this case.

     

    RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC)

     

    because the order is DESC, then the greatest number of pass will be 1 and the final statement pull just those rows, where [rank] = 1.

     

    As you can see it is complicated and there are a lot of calculation (concatenation) that at the end are wasted.

     

    Hope this help and you do not get dizzy by my English.

     

    For you second question, I guess that the problem could be that in order to use WITH as a CTE, you need a semicolon before it in order to tell SS that it is a CTE and not the reserved word used, for example, to specify options.

     

    ;with cte

    as

    ...

     

    AMB

     

     

    Tuesday, October 09, 2007 2:39 PM
    Moderator

All replies

  • Concatenating row values in Transact-SQL

    http://www.projectdmx.com/tsql/rowconcatenate.aspx

     

     

    AMB

    Sunday, October 07, 2007 3:24 PM
    Moderator
  • Many many thanks for your help.

     

    I finally choosed Recursive CTE method  in that article to get results.

     

    Although I'm getting the desired results but the Sql given is very very difficult to understand and I'm still struggling to understand how exactly this is working.

     

    If you have any other supported links to understand this then I will be very thankful.

    Monday, October 08, 2007 10:20 AM
  • Post the question / script you want us to help you understand and we will gladly help you.

     

     

    AMB

    Monday, October 08, 2007 12:12 PM
    Moderator
  • Thanks for your continual support.

     

    Kindly note the link which you provided, from that link I'm using following SQL to achieve results

     

     WITH CTE ( CategoryId, product_list, product_name, length )
              AS ( SELECT CategoryId, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
                     FROM Northwind..Products
                    GROUP BY CategoryId
                    UNION ALL
                   SELECT p.CategoryId, CAST( product_list +
                          CASE WHEN length = 0 THEN '' ELSE ', ' END + ProductName AS VARCHAR(8000) ),
                          CAST( ProductName AS VARCHAR(8000)), length + 1
                     FROM CTE c
                    INNER JOIN Northwind..Products p
                       ON c.CategoryId = p.CategoryId
                    WHERE p.ProductName > c.product_name )
        SELECT CategoryId, product_list
          FROM ( SELECT CategoryId, product_list,
                        RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC )
                   FROM CTE ) D ( CategoryId, product_list, rank )
         WHERE rank = 1 ;

    I have converted this sql as per my db structure and it is giving me the required results but I'm still not able to understand completely how exactly this is working.

     

    Secondly, I need to combine these results with my other SQL. That SQL is having more then 8 Tables with lot of complex joins but yesterday when I tried to join these results with my sql it was giving me error that semicolon is missing.

    Tuesday, October 09, 2007 6:59 AM
  • Hi Vinodonly,

     

    I wonder why you did not use the one that uses "FOR XML PATH('')". It is simpler than the recursive one and faster.

     

    Code Block

    SELECT

    CategoryID,

    STUFF(

    (

    SELECT ',' + ProductName

    FROM dbo.[Products] AS p

    WHERE p.CategoryID = c.CategoryID

    ORDER BY ProductName

    FOR XML PATH('')

    ), 1, 1, '') AS product_list

    FROM

    dbo.[Categories] AS c;

     

     

     

    Anyway, here is how the recursive one works, but let us work with just one CategoryID, so you can see it better. Please execute this statement.

     

    WITH CTE ( CategoryId, product_list, product_name, length )

    AS

    (

    SELECT

    CategoryId,

    CAST('' AS VARCHAR(8000)),

    CAST('' AS VARCHAR(8000)),

    0

    FROM

    Northwind..Products

    WHERE

    [CategoryId] = 1

    GROUP BY

    CategoryId

    UNION ALL

    SELECT

    p.CategoryId,

    CAST( product_list + CASE WHEN length = 0 THEN '' ELSE ', ' END + ProductName AS VARCHAR(8000) ),

    CAST( ProductName AS VARCHAR(8000)),

    length + 1

    FROM

    CTE c

    INNER JOIN

    Northwind..Products p

    ON c.CategoryId = p.CategoryId

    WHERE

    p.ProductName > c.product_name

    )

    SELECT

    CategoryId, product_list, [length], [rank]

    FROM

    (

    SELECT

    CategoryId, product_list, [length],

    RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC)

    FROM

    CTE

    ) D ( CategoryId, product_list, [length], rank )

    ORDER BY

    [length], [rank] DESC

     

    First, the anchor part is pulling distinct [CategoryID] from table dbo.Products and two empty string columns, product_list and product_name, and 0 as lenght. Well, I do not know why they choose [length] instead cycle or pass. If we work with CategoryID = 1, then you will have a result like this from the anchor part:

     

    CategoryId product_list product_name length
    1 0

     

    Now the recursive part bring all ProductName with same CategoryID and ProductName > product_name, in this case all products with same CategoryID and ProductName > ''. Then for each row in the result from the first pass of recursive part, it will concatenate the product_name to the product_list list column from the anchor part. The result of the first pass on the recursive part will be something like:

     

    CategoryId product_list product_name length
    1 Chai Chai 1
    1 Chang Chang 1
    1 Guaraná Fantástica Guaraná Fantástica 1
    1 Sasquatch Ale Sasquatch Ale 1
    1 Steeleye Stout Steeleye Stout 1
    1 Côte de Blaye Côte de Blaye 1
    1 Chartreuse verte Chartreuse verte 1
    1 Ipoh Coffee Ipoh Coffee 1
    1 Laughing Lumberjack Lager Laughing Lumberjack Lager 1
    1 Outback Lager Outback Lager 1
    1 Rhönbräu Klosterbier Rhönbräu Klosterbier 1
    1 Lakkalikööri Lakkalikööri 1

     

    From that result, pull again all products with same CategoryID and ProductName > product_list, but notice that it will be done for each row, so for the product_name = 'Chai', the list will be 'Chang','Guaraná Fantástica', ..., 'Lakkalikööri'. The result will be a string of the concatenation of the previous product_list plus the new product_name. The result of the second pass for product_name 'Chai' will be:

     

    CategoryId product_list product_name length
    1 Chai, Chang Chang 2
    1 Chai, Guaraná Fantástica Guaraná Fantástica 2
    1 Chai, Sasquatch Ale Sasquatch Ale 2
    1 Chai, Steeleye Stout Steeleye Stout 2
    1 Chai, Côte de Blaye Côte de Blaye 2
    1 Chai, Chartreuse verte Chartreuse verte 2
    1 Chai, Ipoh Coffee Ipoh Coffee 2
    1 Chai, Laughing Lumberjack Lager Laughing Lumberjack Lager 2
    1 Chai, Outback Lager Outback Lager 2
    1 Chai, Rhönbräu Klosterbier Rhönbräu Klosterbier 2
    1 Chai, Lakkalikööri Lakkalikööri 2

     

    The same for product_name 'Chang'.

     

    CategoryId product_list product_name length
    1 Chang, Guaraná Fantástica Guaraná Fantástica 2
    1 Chang, Sasquatch Ale Sasquatch Ale 2
    1 Chang, Steeleye Stout Steeleye Stout 2
    1 Chang, Côte de Blaye Côte de Blaye 2
    1 Chang, Chartreuse verte Chartreuse verte 2
    1 Chang, Ipoh Coffee Ipoh Coffee 2
    1 Chang, Laughing Lumberjack Lager Laughing Lumberjack Lager 2
    1 Chang, Outback Lager Outback Lager 2
    1 Chang, Rhönbräu Klosterbier Rhönbräu Klosterbier 2
    1 Chang, Lakkalikööri Lakkalikööri 2

     

    Notice that the number of rows for 'Chai' is greater than the one for 'Chang', this is because of the filter expression. In other words, because the list has to be sort by ProductName, each pass concatenate previous product_list with each one of the products matching ProductName > product_name, were product_name is the name of the product in the last list. At the end, you will have a a lot of rows but the only one you need is the one that include the concatenation of all products for that category, in other words the list with the greates number of products in its list and for each CategoryID. That row si the one with greates [length] or number of pass, and this is being calculated using the ranking function RANK, but you can use any of the ranking functions in this case.

     

    RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC)

     

    because the order is DESC, then the greatest number of pass will be 1 and the final statement pull just those rows, where [rank] = 1.

     

    As you can see it is complicated and there are a lot of calculation (concatenation) that at the end are wasted.

     

    Hope this help and you do not get dizzy by my English.

     

    For you second question, I guess that the problem could be that in order to use WITH as a CTE, you need a semicolon before it in order to tell SS that it is a CTE and not the reserved word used, for example, to specify options.

     

    ;with cte

    as

    ...

     

    AMB

     

     

    Tuesday, October 09, 2007 2:39 PM
    Moderator
  •  

    First of all I must thank you for replying my question in such a detailed way. This is a much better explanation then any manual or BOL  and it is very very clear to me now. (CTE Method)

     

    I must admit that whenever I have posted any questions in this forum, I have always learned something new for which I'm very very thankful.

     

    You are correct about XML path, I just modified the SQL as per my DB and it is working without any problem. Infact, it is much more compact, faster and easier to understand.

     

    Once again I'm very thankful to you for guiding me in right direction and taking out your precious time in explaining me in such detail.

    Tuesday, October 09, 2007 3:22 PM
  • Hi Vinodonly,

     

    Thanks for your comment.

     

    AMB

    Tuesday, October 09, 2007 5:23 PM
    Moderator
  •  

    Sorry for posting in an answered post, Actually the question is related with this same issue only.

     

    If table is having following values

     

    Style 1

    Style 2

    Style 3

     

    Instead of returning result in this way

     

    Style 1, Style 2, Style 3

     

    It should return value in this way

     

    Style 1-2-3

     

    This is a abbreviated way of showing all the 3 style nos. It's very simple to do it if I create CLR proc but I was wondering if this is possible through plain SQL.

    Monday, October 22, 2007 2:33 PM