none
Recursive query? RRS feed

  • Question

  • I have a dataset similar to the one shown below and need to end up with one record which contains all of the descriptions concatenated together in the proper order

    Code            RowNum                  Description

    A                 1                              The quick brown

    A                 2                              fox jumped

    A                 3                             over

    A                4                              the

    A                 5                             lazy dog

     

    The final ourput I need is this.

    Code                   Description

    A                        The quick brown fox jumped over the lazy dog.

    Any idea how I can do this in T-SQL?  This of course is just an example of my data and some codes may only have 1 description associated with them and some codes have up to 12, so it is not constant.

    Any help is appreciated.  Thanks!

    Wednesday, November 3, 2010 7:59 PM

Answers

  • Take a read of this blog post

    Making a list and checking it twice

    And you may also want to read these two blogs in regards to performance testing of the above solution.

    Concatenating Rows - Part 1

    Concatenating Rows - Part 2

    Your query should be

    select [Code], stuff((select ' ' + Description 
    from SourceTable S1 where S.[Code] = S1.[Code] 
    order by RowNum for XML PATH('')),1,1,'') as FullDescription
    
    from SourceTable S GROUP BY [Code]


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Tuesday, November 9, 2010 5:48 AM
    Wednesday, November 3, 2010 8:08 PM
    Moderator
  • CREATE TABLE #A
    (CODE VARCHAR(3),
    ROWNUM INT,
    DESCRIPTION VARCHAR(40))
    
    INSERT INTO #A VALUES ('A', 1, 'i AM')
    INSERT INTO #A VALUES ('A', 2, 'bADANI')
    
    
    select code, substring((SELECT CONVERT(VARCHAR(10), ' ' + DESCRIPTION)
                                   FROM  #a
                                   
                                   GROUP BY code, DESCRIPTION
                                   --order by rownum
                                   
                                   FOR XML PATH( '' )), 2, 200000)
                                   from #a
                                   
                                   group by CODE
    

    • Marked as answer by KJian_ Tuesday, November 9, 2010 5:49 AM
    Wednesday, November 3, 2010 8:13 PM
  • You can avoid using function STUFF, by using a column name with the path specified as "data()".

    SELECT
      Code,
      (
      SELECT S1.[Description] AS [data()]
      FROM SourceTable AS S1
      WHERE S.Code = S1.Code 
      ORDER BY S1.RowNum
      FOR XML PATH(''), TYPE
      ).value('.', 'varchar(max)') AS FullDescription
    FROM
      SourceTable AS S
    GROUP BY
      Code;
    GO

    Also, use TYPE subclause and later convert it to varchar, in order to avoid those not allowed characters inside the xml doc.

    SELECT
      (
      SELECT c1 AS [data()] 
      FROM (SELECT 'A <>' AS c1 UNION ALL SELECT 'B') AS T 
      ORDER BY c1 
      FOR XML PATH('')
      ) AS c1,
      (
      SELECT c1 AS [data()] 
      FROM (SELECT 'A <>' AS c1 UNION ALL SELECT 'B') AS T 
      ORDER BY c1 
      FOR XML PATH(''), TYPE
      ).value('.', 'varchar(25)') AS c2;
    GO

    Column Names with the Path Specified as data()

     


    AMB

    Some guidelines for posting questions...

    • Proposed as answer by Naomi NModerator Wednesday, November 3, 2010 11:54 PM
    • Marked as answer by KJian_ Tuesday, November 9, 2010 5:49 AM
    Wednesday, November 3, 2010 11:24 PM
    Moderator

All replies

  • You have to use XML Path.

    Just wait for 5 mins. I will put the code.

    Wednesday, November 3, 2010 8:04 PM
  • Take a read of this blog post

    Making a list and checking it twice

    And you may also want to read these two blogs in regards to performance testing of the above solution.

    Concatenating Rows - Part 1

    Concatenating Rows - Part 2

    Your query should be

    select [Code], stuff((select ' ' + Description 
    from SourceTable S1 where S.[Code] = S1.[Code] 
    order by RowNum for XML PATH('')),1,1,'') as FullDescription
    
    from SourceTable S GROUP BY [Code]


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Tuesday, November 9, 2010 5:48 AM
    Wednesday, November 3, 2010 8:08 PM
    Moderator
  • CREATE TABLE #A
    (CODE VARCHAR(3),
    ROWNUM INT,
    DESCRIPTION VARCHAR(40))
    
    INSERT INTO #A VALUES ('A', 1, 'i AM')
    INSERT INTO #A VALUES ('A', 2, 'bADANI')
    
    
    select code, substring((SELECT CONVERT(VARCHAR(10), ' ' + DESCRIPTION)
                                   FROM  #a
                                   
                                   GROUP BY code, DESCRIPTION
                                   --order by rownum
                                   
                                   FOR XML PATH( '' )), 2, 200000)
                                   from #a
                                   
                                   group by CODE
    

    • Marked as answer by KJian_ Tuesday, November 9, 2010 5:49 AM
    Wednesday, November 3, 2010 8:13 PM
  • You can avoid using function STUFF, by using a column name with the path specified as "data()".

    SELECT
      Code,
      (
      SELECT S1.[Description] AS [data()]
      FROM SourceTable AS S1
      WHERE S.Code = S1.Code 
      ORDER BY S1.RowNum
      FOR XML PATH(''), TYPE
      ).value('.', 'varchar(max)') AS FullDescription
    FROM
      SourceTable AS S
    GROUP BY
      Code;
    GO

    Also, use TYPE subclause and later convert it to varchar, in order to avoid those not allowed characters inside the xml doc.

    SELECT
      (
      SELECT c1 AS [data()] 
      FROM (SELECT 'A <>' AS c1 UNION ALL SELECT 'B') AS T 
      ORDER BY c1 
      FOR XML PATH('')
      ) AS c1,
      (
      SELECT c1 AS [data()] 
      FROM (SELECT 'A <>' AS c1 UNION ALL SELECT 'B') AS T 
      ORDER BY c1 
      FOR XML PATH(''), TYPE
      ).value('.', 'varchar(25)') AS c2;
    GO

    Column Names with the Path Specified as data()

     


    AMB

    Some guidelines for posting questions...

    • Proposed as answer by Naomi NModerator Wednesday, November 3, 2010 11:54 PM
    • Marked as answer by KJian_ Tuesday, November 9, 2010 5:49 AM
    Wednesday, November 3, 2010 11:24 PM
    Moderator
  • Use XML or some other tools -- text or document would be so much better. Then read that chapter on First Normal Form you skipped when you took a RDBMS course in school so you will know why this soooooo awful. 
    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Thursday, November 4, 2010 12:39 AM
  •  

    I am afraid Reporting Services does not have comma-delimited list preparation function for data, although that would be a very good feature. 

    On the other hand it has the JOIN function which prepares the list for multi-value parameter selection (to be passed as a string input parameter of a stored procedure).

    The common approach is to prepare the list in the stored procedure which serves as dataset for the report or pull-in some custom-code.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Thursday, November 4, 2010 9:39 PM
    Moderator
  • My version is here

    declare @str varchar(8000)
    set @str=''
    select @str=@str+q.[description] from
    (
    	select'A' as Code,1 RowNum ,'The quick brown' [Description] union all
    	select'A',2,'fox jumped' union all
    	select'A',3,'over' union all
    	select'A',4,'the' union all
    	select'A',5,'lazy dog' 
    )q
    select @str
    
    
    
    
    Wednesday, December 1, 2010 4:17 PM