none
On String Aggregate Concatenation RRS feed

Answers

  • SQL Server MVP Anith Sen has the standard reference on the topic:
    http://www.projectdmx.com/tsql/rowconcatenate.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

    Tuesday, July 6, 2010 8:59 PM
  • I posted it before. Anyway, here it's another time


    http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html

    http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, July 6, 2010 11:24 PM
    Moderator
  • Use the TYPE directive to produce an XML column, and then the .value function to convert to a varchar, as in

    WITH cte As
    (SELECT id,
     (SELECT ','+name AS [text()]
     FROM t1
     WHERE id = T.id
     ORDER BY name
     FOR XML PATH(''), TYPE
     ) AS concat
     FROM t1 AS T)
     SELECT DISTINCT c.id, 
      Stuff(c.concat.value('.', 'varchar(8000)'), 1, 2, '') As concat
     FROM cte c;
    
    Tom

    Monday, July 5, 2010 5:10 AM
  • DECLARE	@Sample TABLE
    	(
    		ID INT,
    		Data NVARCHAR(100)
    	)
    
    INSERT	@Sample
    VALUES	(1, 'Peso & Performance SQL'),
    	(1, 'MVP'),
    	(2, 'Need help <' + NCHAR(2) + '? /> + ' + NCHAR(31) + ' f'),
    	(2, 'With XML string concatenation ?')
    
    ;WITH cteSource(ID, Content)
    AS (
    	SELECT		i.ID,
    			f.Content.value('.', 'NVARCHAR(MAX)')
    	FROM		(
    				SELECT		ID
    				FROM		@Sample
    				GROUP BY	ID
    			) AS i
    	CROSS APPLY	(
    				SELECT	CAST(', ' + w.Data AS VARBINARY(MAX))
    				FROM	@Sample AS w
    				WHERE	w.ID = i.ID
    				FOR XML	PATH(''),
    					TYPE
    			) AS f(Content)
    )
    SELECT	ID,
    	STUFF(CAST(Content AS NVARCHAR(MAX)), 1, 2, '') AS Content
    FROM	(
    		SELECT	ID,
    			CAST(N'' AS XML).value('xs:base64Binary(sql:column("Content"))', 'VARBINARY(MAX)') AS Content	
    		FROM	cteSource
    	) AS d
    Monday, July 5, 2010 5:23 AM
  • One another way of doing this without XML Path

    DECLARE @TBL TABLE

    (

     ID INT

    ,NAME VARCHAR(50)

    )

     

    INSERT INTO @TBL

    SELECT 1,'A'

    UNION

    SELECT 1,'B'

    UNION

    SELECT 2,'C'

    UNION

    SELECT 2,'D';

     

     

    WITH ConcatCTE

      AS

      (

      SELECT ID,NAME,RNK,Concatval = CONVERT(VARCHAR(MAX),NAME)

        FROM (SELECT  ID,NAME,RNK = RANK() OVER(PARTITION BY ID ORDER BY NAME)

                      FROM @TBL) ConcatbaseCTE

       WHERE RNK = 1

      UNION ALL

      SELECT ConcatbaseCTE.ID,ConcatbaseCTE.NAME,ConcatbaseCTE.RNK,Concatval =CONVERT(VARCHAR(MAX),ConcatCTE.Name + ',' + ConcatbaseCTE.NAME)

        FROM (SELECT  ID,NAME,RNK = RANK() OVER(PARTITION BY ID ORDER BY NAME)

                      FROM @TBL) ConcatbaseCTE

        JOIN ConcatCTE

          ON ConcatbaseCTE.ID = ConcatCTE.ID

         AND ConcatbaseCTE.RNK = ConcatCTE.RNK + 1

      )

     

      SELECT ID,MAX(Concatval)

        FROM ConcatCTE

       GROUP BY ID

     

     

    Monday, July 5, 2010 9:17 AM

All replies