Click here to monitor SSC
  • Av rating:
  • Total votes: 462
  • Total comments: 59
Anith Sen

Concatenating Row Values in Transact-SQL

31 July 2008

It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate. How do you go about producing a summary result in which a distinguishing column from each row in each particular category is listed in a 'aggregate' column? A simple, and intuitive way of displaying data is surprisingly difficult to achieve. Anith Sen gives a summary of different ways, and offers words of caution over the one you choose.

Contents

Introduction

Many a time, SQL programmers are faced with a requirement to generate report-like resultsets directly from a Transact SQL query. In most cases, the requirement arises from the fact that there neither sufficient tools nor in-house expertise to develop tools that can extract the data as a resultset, and then massage the data in the desired display format. Quite often folks are confused about the potential of breaking relational fundamentals such as the First Normal Form or the scalar nature of typed values. (Talking about 1NF violations in a language like SQL which lacks sufficient domain support, allows NULLs and supports duplicates is somewhat ironic to begin with, but that is a topic which requires detailed explanations.)

By 'Concatenating row values' we mean this:
You have a table, view or result that looks like this...
...and you wish to have a resultset like the one below:

In this example we are accessing the sample NorthWind database and using the following SQL


SELECT CategoryId, ProductName

      FROM Northwind..Products

The objective is to return a resultset with two columns, one with the Category Identifier, and the other with a concatenated list of all the Product Names separated by a delimiting character: such as a comma.

Concatenating column values or expressions from multiple rows are usually best done in a client side application language, since the string manipulation capabilities of Transact SQL and SQL based DBMSs are somewhat limited. However, you can do these using different approaches in Transact SQL, but it is best to avoid such methods in long-term solutions

A core issue

Even though SQL, in general, deviates considerably from the relational model, its reliance on certain core aspects of relational foundations makes SQL functional and powerful. One such core aspect is the set based nature of SQL expressions (well, multi-sets to be exact, but for the given context let us ignore the issue of duplication). The primary idea is that tables are unordered and therefore the resultsets of any query that does not have an explicit ORDER BY clause is unordered as well. In other words, the rows in a resultset of a query do not have a prescribed position, unless it is explicitly specified in the query expression.

On the other hand, a concatenated list is an ordered structure. Each element in the list has a specific position. In fact, concatenation itself is an order-utilizing operation in the sense that values can be prefixed or post fixed to an existing list. So approaches that are loosely called “concatenating row values”, “aggregate concatenation” etc. would have to make sure that some kind of an order, either explicit or implicit, should be specified prior to concatenating the row values. If such an ordering criteria is not provided, the concatenated string would be arbitrary in nature.

Considerations

Generally, requests for row value concatenations often comes in two basic flavors, when the number of rows is known and small (typically less than 10) and when the number of rows is unknown and potentially large. It may be better to look at each of them separately.

In some cases, all the programmer wants is just the list of values from a set of rows. There is no grouping or logical partitioning of values such as  the list of email addresses separated by a semicolon or some such. In such situations, the approaches can be the same except that the join conditions may vary. Minor variations of the examples list on this page illustrate such solutions as well.

For the purpose of this article the Products table from Northwind database is used to illustrate column value concatenations with a grouping column. Northwind is a sample database in SQL Server 2000 default installations. You can download a copy from from the Microsoft Downloads

Concatenating values when the number of items is small and known beforehand


When the number of rows is small and almost known beforehand, it is easier to generate the code. One common approach where there is a small set of finite rows is the pivoting method. Here is an example where only the first four alphabetically-sorted product names per categoryid is retrieved:

   SELECT CategoryId,

           MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' +

           MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' +

           MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' +

           MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END )

      FROM ( SELECT p1.CategoryId, p1.ProductName,

                    ( SELECT COUNT(*)

                        FROM Northwind.dbo.Products p2

                        WHERE p2.CategoryId = p1.CategoryId

                        AND p2.ProductName <= p1.ProductName )

             FROM Northwind.dbo.Products p1 ) D ( CategoryId, ProductName, seq )

     GROUP BY CategoryId ;

 The idea here is to create a expression inside the correlated subquery that produces a rank (seq) based on the product names and then use it in the outer query. Using common table expressions and the ROW_NUMBER() function, you can re-write this as:

; WITH CTE ( CategoryId, ProductName, seq )

     AS ( SELECT p1.CategoryId, p1.ProductName,

            ROW_NUMBER() OVER ( PARTITION BY CategoryId ORDER BY ProductName )

           FROM Northwind.dbo.Products p1 )

SELECT CategoryId,

           MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' +

           MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' +

           MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' +

           MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END )

     FROM CTE

     GROUP BY CategoryId ;

 Note that ROW_NUMBER() is a newly-introduced feature in SQL 2005. If you are using any previous version, you will have to use the subquery approach (You can also use a self-join, to write it a bit differently). Using the recently introduced PIVOT operator, you can write this as follows:

SELECT CategoryId,

           "1" + ', ' + "2" + ', ' + "3" + ', ' + "4" AS Product_List

      FROM ( SELECT CategoryId, ProductName,

                    ROW_NUMBER() OVER (PARTITION BY CategoryId
             ORDER
BY ProductName)

               FROM Northwind.dbo.Products ) P ( CategoryId, ProductName, seq )

     PIVOT ( MAX( ProductName ) FOR seq IN ( "1", "2", "3", "4" ) ) AS P_ ;

Not only does the syntax appear a bit confusing, but also it does not seem to offer any more functionality than the previous CASE approach. However, in rare situations, it could come in handy.

Concatenating values when the number of items is not known

When you do not know the number of items that are to be concatenated beforehand, the code can become rather  more demanding. The new features in SQL 2005 make some of the approaches easier. For instance, the recursive common table expressions (CTEs) and the FOR XML PATH('') syntax makes the server do the hard work behind the concatenation, leaving the programmer to deal with the presentation issues. The examples below make this point obvious.

Recursive CTE methods

The idea behind this method is from a newsgroup posting by Vadim Tropashko. It is similar to the ideas behind generating a materialized path for hierarchies.

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 ;

 The CASE in the recursive part of the CTE is used to eliminate the initial comma, but you can use RIGHT or the SUBSTRING functions instead. This may not be the best performing option, but certain additional tuning could be done to make them suitable for medium sized datasets.

Another approach using recursive common table expressions was sent in by Anub Philip, an Engineer from Sathyam Computers that uses separate common table expressions for the anchor and recursive parts.

WITH Ranked ( CategoryId, rnk, ProductName ) 

             AS ( SELECT CategoryId,

                         ROW_NUMBER() OVER( PARTITION BY CategoryId ORDER BY CategoryId ),

                         CAST( ProductName AS VARCHAR(8000) )

                    FROM Northwind..Products),

   AnchorRanked ( CategoryId, rnk, ProductName ) 

             AS ( SELECT CategoryId, rnk, ProductName

                    FROM Ranked

                   WHERE rnk = 1 ),

RecurRanked ( CategoryId, rnk, ProductName )

             AS ( SELECT CategoryId, rnk, ProductName

                    FROM AnchorRanked

                   UNION ALL

                  SELECT Ranked.CategoryId, Ranked.rnk,

                         RecurRanked.ProductName + ', ' + Ranked.ProductName

                    FROM Ranked

                   INNER JOIN RecurRanked

                      ON Ranked.CategoryId = RecurRanked.CategoryId

                     AND Ranked.rnk = RecurRanked.rnk + 1 )

SELECT CategoryId, MAX( ProductName )

      FROM RecurRanked

  GROUP BY CategoryId;

On first glance, this query may seem a bit expensive in comparison, but the reader is encouraged to check the execution plans and make any additional tweaks as needed.

The blackbox XML methods

Here is a technique for string concatenation that uses the FOR XML clause with PATH mode. It was initially posted by Eugene Kogan, and later became common in public newsgroups.

SELECT p1.CategoryId,

       ( SELECT ProductName + ','

           FROM Northwind.dbo.Products p2

          WHERE p2.CategoryId = p1.CategoryId

          ORDER BY ProductName

            FOR XML PATH('') ) AS Products

      FROM Northwind.dbo.Products p1

      GROUP BY CategoryId ;

 

There is a similar approach that was originally found in the beta newsgroups, using the CROSS APPLY operator.

SELECT DISTINCT CategoryId, ProductNames

    FROM Northwind.dbo.Products p1

   CROSS APPLY ( SELECT ProductName + ','

                     FROM Northwind.dbo.Products p2

                     WHERE p2.CategoryId = p1.CategoryId

                     ORDER BY ProductName

                     FOR XML PATH('') )  D ( ProductNames )

You may notice a comma at the end of the concatenated string, which you can remove using a STUFF, SUBSTRING or LEFT function. While the above methods are deemed reliable by many at the time of writing, there is no guarantee that it will stay that way, given that the internal workings and evaluation rules of FOR XML PATH() expression in correlated subqueries are not well documented.

The problem with this approach is that the contents of the ProductName column is interpreted a XML rather than text, which will lead to  certain characters being 'entitized', or in some cases, leading to the SQL causing an error. (see note below, and solution by Adam Machanic in comments below) and to avoid this, it is better to use a slightly revised syntax like this..

SELECT p1.CategoryId,

       stuff( (SELECT ','+ProductName

               FROM Northwind.dbo.Products p2

               WHERE p2.CategoryId = p1.CategoryId

               ORDER BY ProductName

               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

            ,1,1,'')

       AS Products

      FROM Northwind.dbo.Products p1

      GROUP BY CategoryId ;

...and this...

SELECT DISTINCT CategoryId, ProductNames

    FROM Northwind.dbo.Products p1

   CROSS APPLY ( SELECT

       stuff( (SELECT ','+ProductName

               FROM Northwind.dbo.Products p2

               WHERE p2.CategoryId = p1.CategoryId

               ORDER BY ProductName

               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

            ,1,1,'')

    )  D ( ProductNames )

Using Common Language Runtime

Though this article is about approaches using Transact SQL, this section is included due to the popularity of CLR aggregates in SQL 2005. It not only empowers the CLR programmer with new options for database development, but also, in some cases, they work at least as well as native Transact SQL approaches.

If you are familiar with .NET languages, SQL 2005 offers a convenient way to create user defined aggregate functions using C#, VB.NET or similar languages that are supported by the Common Language Runtime (CLR). Here is an example of a string concatenate aggregate function written using C#.

using System;

using System.Collections.Generic;

using System.Data.SqlTypes;

using System.IO;

using Microsoft.SqlServer.Server;

 

[Serializable]

[SqlUserDefinedAggregate(Format.UserDefined,  MaxByteSize=8000)]

public struct strconcat : IBinarySerialize{

        private List values;

 

        public void Init()    {

            this.values = new List();

        }

 

        public void Accumulate(SqlString value)    {

            this.values.Add(value.Value);

        }

 

        public void Merge(strconcat value)    {

            this.values.AddRange(value.values.ToArray());

        }

 

        public SqlString Terminate()    {

            return new SqlString(string.Join(", ", this.values.ToArray()));

        }

 

        public void Read(BinaryReader r)    {

            int itemCount = r.ReadInt32();

            this.values = new List(itemCount);

            for (int i = 0; i <= itemCount - 1; i++)    {

                this.values.Add(r.ReadString());

            }

        }

 

        public void Write(BinaryWriter w)    {

            w.Write(this.values.Count);

            foreach (string s in this.values)      {

                w.Write(s);

            }

        }

}

Once you build and deploy this assembly on the server, you should be able to execute your concatenation query as:

SELECT CategoryId,

           dbo.strconcat(ProductName)

      FROM Products

     GROUP BY CategoryId ;

 

 If you are a total newbie on CLR languages, and would like to learn more about developing database solutions using CLR languages, consider starting at Introduction to Common Language Runtime (CLR) Integration

Scalar UDF with recursion

Recursive functions in t-SQL have a drawback that the maximum nesting level is 32. So this approach is applicable only for smaller datasets, especially when the number of items within a group, that needs to be concatenated, is less than 32.

 

CREATE FUNCTION udf_recursive ( @cid INT, @i INT )

RETURNS VARCHAR(8000) AS BEGIN

        DECLARE @r VARCHAR(8000), @l VARCHAR(8000)

        SELECT @i = @i - 1,  @r = ProductName + ', '

          FROM Northwind..Products p1

         WHERE CategoryId = @cid

           AND @i = ( SELECT COUNT(*) FROM Northwind..Products p2

                       WHERE p2.CategoryId = p1.CategoryId

                         AND p2.ProductName <= p1.ProductName ) ;

        IF @i > 0 BEGIN

              EXEC @l = dbo.udf_recursive @cid, @i ;

              SET @r =  @l + @r ;

END

RETURN @r ;

END

This function can be invoked as follows:

SELECT CategoryId,

           dbo.udf_recursive( CategoryId, COUNT(ProductName) )

      FROM Northwind..Products

     GROUP BY CategoryId ;

Table valued UDF with a WHILE loop

This approach is based on the idea by Linda Wierzbecki where a table variable with three columns is used within a table-valued UDF. The first column represents the group, second represents the currently processing value within a group and the third represents the concatenated list of values.

 

CREATE FUNCTION udf_tbl_Concat() RETURNS @t TABLE(

            CategoryId INT,

            Product VARCHAR(40),

            list VARCHAR(8000) )

BEGIN

     INSERT @t (CategoryId, Product, list)

     SELECT CategoryId, MIN(ProductName),  MIN(ProductName)

       FROM Products

      GROUP BY CategoryId

WHILE ( SELECT COUNT(Product) FROM @t ) > 0 BEGIN

        UPDATE t

           SET list = list + COALESCE(

                         ( SELECT ', ' + MIN( ProductName )

                             FROM Northwind..Products

                            WHERE Products.CategoryId = t.CategoryId

                              AND Products.ProductName > t.Product), ''),

               Product = ( SELECT MIN(ProductName)

                             FROM Northwind..Products

                            WHERE Products.CategoryId = t.CategoryId

                              AND Products.ProductName > t.Product )

          FROM @t t END

RETURN

END

 

 The usage of the above function can be like:

SELECT CategoryId, list AS Products

  FROM udf_tbl_Concat() ;

 Dynamic SQL

This approach is a variation of the kludge often known using the nickname of  ‘dynamic cross tabulation’. There is enough literature out there which demonstrates the drawbacks and implications of using Dynamic SQL. A popular one, at least from Transact SQL programmer’s perspective, is Erland's Curse and Blessings of Dynamic SQL. The Dynamic SQL approaches can be developed based on creating a Transact SQL query string based on the number of groups and then use a series of CASE expressions or ROW_NUMBER() function to pivot the data for concatenation.

DECLARE @r VARCHAR(MAX), @n INT, @i INT

SELECT @i = 1,

       @r = 'SELECT CategoryId, ' + CHAR(13),

       @n = (SELECT TOP 1 COUNT( ProductName )

                   FROM Northwind..Products

                  GROUP BY CategoryId

                  ORDER BY COUNT( ProductName ) DESC ) ;     

WHILE @i <= @n BEGIN

           SET @r = @r +

           CASE WHEN @i =

                THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + '

                                 THEN ProductName

                                            ELSE SPACE(0) END ) + ' + CHAR(13)

           WHEN @i = @n

             THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + '

                                 THEN '', '' + ProductName

                                 ELSE SPACE(0) END ) ' + CHAR(13)

             ELSE 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + '

                                 THEN '', '' + ProductName

                                 ELSE SPACE(0) END ) + ' + CHAR(13) 

           END ;

           SET @i = @i + 1 ;

END

SET @r = @r + '

    FROM ( SELECT CategoryId, ProductName,

                  ROW_NUMBER() OVER ( PARTITION BY CategoryId ORDER BY ProductName )

             FROM Northwind..Products p ) D ( CategoryId, ProductName, Seq )

           GROUP BY CategoryId;'

EXEC( @r ) ;

 The Cursor approach

The drawbacks of rampant usage of cursors are well-known among the Transact SQL community. Because they are generally resource intensive, procedural and inefficient, one should strive to avoid cursors or loop based solutions in general Transact SQL programming.

DECLARE @tbl TABLE (id INT PRIMARY KEY, list VARCHAR(8000))

SET NOCOUNT ON

DECLARE @c INT, @p VARCHAR(8000), @cNext INT, @pNext VARCHAR(40)

DECLARE c CURSOR FOR

        SELECT CategoryId, ProductName

          FROM Northwind..Products

         ORDER BY CategoryId, ProductName ;

        OPEN c ;

        FETCH NEXT FROM c INTO @cNext, @pNext ;

        SET @c = @cNext ;

        WHILE @@FETCH_STATUS = 0 BEGIN

             IF @cNext > @c BEGIN

                  INSERT @tbl SELECT @c, @p ;

                  SELECT @p = @PNext, @c = @cNext ;

             END ELSE

                  SET @p = COALESCE(@p + ',', SPACE(0)) + @pNext ;

             FETCH NEXT FROM c INTO @cNext, @pNext

        END

        INSERT @tbl SELECT @c, @p ;

        CLOSE c ;

DEALLOCATE c ;

SELECT * FROM @tbl ;

 

 Unreliable approaches

This section details a couple of notorious methods often publicized by some in public forums. The problem with these methods is that they rely on the physical implementation model; changes in indexes, statistics etc or even a change of a simple expression in the SELECT list or ORDER BY clause can change the output. Also these are undocumented, unsupported and unreliable to the point where one can consistently demonstrate failures. Therefore these methods are not recommended at all for production mode systems.

Scalar UDF with t-SQL update extension

It is rare for the usage of an expression that involves a column, a variable and an expression in the SET clause in an UPDATE statement to appear intuitive. However, in general, the optimizer often seems to process these values in the order of materialization, either in the internal work tables or any other storage structures.

CREATE FUNCTION udf_update_concat (@CategoryId INT)

        RETURNS VARCHAR(MAX) AS

BEGIN

DECLARE @t TABLE(p VARCHAR(40));

DECLARE @r VARCHAR(MAX) ;

        SET @r = SPACE(0) ;

        INSERT @t ( p ) SELECT ProductName FROM Northwind..Products

                         WHERE CategoryId = @CategoryId ;

        IF @@ROWCOUNT > 0

            UPDATE @t

               SET @r = @r + p + ',' ;

        RETURN(@r)

END

Here is how to use this function:

SELECT CategoryId, dbo.udf_update_concat(CategoryId)

      FROM Northwind..Products

     GROUP BY CategoryId ;

 

Again, it is important to consider that lack of physical independence that is being exploited here before using or recommending this as a usable and meaningful solution.

Scalar UDF with variable concatenation in SELECT

This is an approach purely dependent on the physical implementation and internal access paths. Before using this approach, make sure to refer to the relevant knowledgebase article.

CREATE FUNCTION dbo.udf_select_concat ( @c INT )

RETURNS VARCHAR(MAX) AS BEGIN

DECLARE @p VARCHAR(MAX) ;

           SET @p = '' ;

        SELECT @p = @p + ProductName + ','

          FROM Northwind..Products

         WHERE CategoryId = @c ;

RETURN @p

END

And, as for its usage:

SELECT CategoryId, dbo.udf_select_concat( CategoryId )

      FROM Northwind..Products

     GROUP BY CategoryId ;

Conclusion

Regardless of how it is used, "aggregate concatenation" of row values in Transact SQL, especially when there is a grouping, is not a simple routine. You need to consider carefully the circumstances  before you choose one method over another.  The most logical choice would to have a built-in operator with optional configurable parameters that can do the concatenation of the values depending on the type. Till then, reporting requirements and external data export routines will have to rely on such Transact SQL programming hacks.

References

Acknowledgements

Umachandar Jayachandran, Linda Wierzbecki, Bruce Margolin, Roy Harvey, Eugene Kogan, Vadim Tropashko, Anub Philip.

Additional information about the XML Blackbox approach inserted 22/03/2012.

Anith Sen

Author profile:

Anith S Larson specializes in data management primarily using SQL Server. From the mid 90s, he has been working on a variety of database design and systems development projects for clients primarily in wealth management and financial services industry. He resides in Lakeland, TN.

Search for other articles by Anith Sen

Rate this article:   Avg rating: from a total of 462 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: My Solution
Posted by: DaveTheWave (view profile)
Posted on: Thursday, July 31, 2008 at 3:49 PM
Message:

I use the following. This is not completely my code. I found most if not nearly all of this on the net.

CREATE TABLE ParameterKeys
  
(
    
ParameterKey INT IDENTITY
                    
PRIMARY KEY,
    
DateCreated DATETIME NOT NULL
   )

CREATE TABLE Param_Concatenation
  
(
    
ParameterKey INT REFERENCES ParameterKeys (ParameterKey)
      
ON DELETE CASCADE,
    
ValueKey VARCHAR(50) NOT NULL,
    
ValueSubKey1 VARCHAR(50) NULL,
    
ValueSubKey2 VARCHAR(50) NULL,
    
Value VARCHAR(50) NOT NULL
   )
GO
CREATE PROCEDURE Param_GetKey
  
@ParameterKey INT OUTPUT
AS
   DELETE   FROM
ParameterKeys
  
WHERE    DateCreated < DATEADD(DAY, -7, GETDATE())
  
INSERT   INTO ParameterKeys
            
(DateCreated)
  
VALUES   (GETDATE())
  
SET @ParameterKey = SCOPE_IDENTITY()
GO

CREATE PROCEDURE Param_DeleteKey @ParameterKey INT
AS
   DELETE   FROM
ParameterKeys
  
WHERE    ParameterKey = @ParameterKey
GO

CREATE FUNCTION fn_Concatenate
  
(
    
@ParameterKey INT,
    
@ValueKey VARCHAR(50),
    
@ValueSubKey1 VARCHAR(50),
    
@ValueSubKey2 VARCHAR(50),
    
@Delimiter VARCHAR(50)
   )
RETURNS VARCHAR(8000)
AS BEGIN
      DECLARE
@tempTable TABLE (value VARCHAR(50))
      
DECLARE @result VARCHAR(8000)
      
SET @result = 'PlaceHolderDummyText'

      
INSERT   @tempTable
              
SELECT   Value
              
FROM     Param_Concatenation
              
WHERE    ParameterKey = @ParameterKey
                
AND ValueKey = @ValueKey
                
AND ValueSubKey1 = @ValueSubKey1
                
AND ValueSubKey2 = @ValueSubKey2
              
ORDER BY Value ASC

      IF
@@ROWCOUNT > 0
        
UPDATE   @tempTable
        
SET      @result = @result + @Delimiter + ' ' + value

      
RETURN (REPLACE(
              
REPLACE(@result, 'PlaceHolderDummyText, ', ''),
              
'PlaceHolderDummyText',
                
''  )
            )
  
END
/**
USAGE
If the ORDERSDETAIL table contains the following data:

ORDERID FROM_TYPE FROM_COL ...
...
65748  99        5186
65748  99        5803
65748  99        5804
65748  99        5806
65748  99        5808
65748  99        5811
65748  99        5812
65748  99        9916
65748  99        9919
65748  99        9920
44055  99        5186
44055  99        5803
44055  99        5804
44055  99        5805
44055  99        5806
44055  99        5808
44055  99        5809
44055  99        5810
44055  99        5811
44055  99        5812
...
*/


DECLARE @ParameterKey INT
EXEC
dbo.Param_GetKey @ParameterKey OUTPUT


INSERT INTO Param_Concatenation
SELECT DISTINCT
      
ParameterKey = @ParameterKey,
      
ValueKey = od.ORDERID,
      
ValueSubKey1 = 'FROM_TYPE',
      
ValueSubKey2 = 'x',
      
Value = od.FROM_COL
  
FROM ORDERSDETAIL od
  
WHERE od.ORDERID IN ('65748','44055')

SELECT dbo.fn_Concatenate(  @ParameterKey,
                          
'65748',
                          
'FROM_TYPE',
                          
'x',
                          
',')
SELECT dbo.fn_Concatenate(  @ParameterKey,
                          
'44055',
                          
'FROM_TYPE',
                          
'x',
                          
',')

--Would product the following output
--Order 65748 would results will be:
---5186, 5803, 5804, 5806, 5808, 5811, 5812, 9916, 9919, 9920
--Order 44055 would results will be:
---5186, 5803, 5804, 5805, 5806, 5808, 5809, 5810, 5811, 5812

Subject: Ooh! This looks fun
Posted by: Phil Factor (view profile)
Posted on: Thursday, July 31, 2008 at 4:16 PM
Message: Here is my method. It works in both SQL Server 2000 and 2005. Goodness me, Anith, you're going to hate it.

DECLARE @accumulation VARCHAR(7000),
--variable used for accumulating lists
@CategoryID INT
--variable used for keeping tabs on the GROUPING id

DECLARE @grouping TABLE--temporary table
(
  
MyID INT IDENTITY(1, 1) PRIMARY KEY,
  
CategoryID INT,
  
ProductName VARCHAR(255),
  
accumulation VARCHAR(7000)--used to collect the list
)
INSERT INTO @Grouping --insert raw result you want a GROUPING of
(CategoryID, ProductName)
  
SELECT CategoryID, ProductName
      
FROM Northwind..Products
      
ORDER BY CategoryID, ProductName

UPDATE @grouping --and update the table, doing the accumulation.
  
SET @Accumulation = [accumulation]
      
= COALESCE(
              
CASE WHEN CategoryID <> COALESCE(@CategoryID, 0)
              
THEN '' + productName
              
ELSE LEFT(@Accumulation + ',' + productName, 7000)
          
END,
          
''),
      
@CategoryID = CategoryID

SELECT CategoryID, MAX(accumulation)
  
FROM @grouping
  
GROUP BY CategoryID
  
ORDER BY CategoryID

Subject: CLR methods
Posted by: Adam Machanic (view profile)
Posted on: Friday, August 01, 2008 at 9:09 AM
Message: Nice to see you here, Anith!

Regarding the CLR method, if you're not afraid to work with some in-memory structures you can both greatly improve performance and exceed the 8000-byte barrier. I suspect that FOR XML PATH will still deliver better performance in many cases, though.

One caveat of FOR XML PATH method is the issue of "entitization." Try the following:---
SELECT
  
n + ',' AS [text()]
FROM
(
  
SELECT 'a<b' AS n
  
UNION ALL
  
SELECT 'b>a'
) r
FOR XML PATH('')
---

...

TO fix this problem we need to play some rather annoying games:

---
SELECT
(
  
SELECT
  
(
    
SELECT
      
n + ',' AS [text()]
    
FROM
    
(
      
SELECT 'a<b' AS n
      
UNION ALL
      
SELECT 'b>a'
    
) r
    
FOR XML PATH(''), TYPE
  
) AS concat
  
FOR XML RAW, TYPE
).value('/row[1]/concat[1]', 'varchar(max)')
---

<plug>
I wrote about the CLR method in detail in "Expert SQL Server 2005 Development" (Apress, 2007)
</plug>


Subject: Re: Ooh! This looks fun
Posted by: Anith Sen (not signed in)
Posted on: Friday, August 01, 2008 at 9:31 AM
Message: Good job, Phil.

Since you are using a table variable you have control over the underlying indexes and so you don't have to worry about somebody else changing them and affecting the output.

I never completely understood the underlying evaluation scheme in t-SQL UPDATE EXTENSION. Is it right to left, all at once, random, I have no idea. Regardless, it seems to work fine here.

Another concern is the INSERT .. SELECT statement with an ORDER BY clause. Again, in this case, since you are using a table variable it seems like there shouldn't be any problems. There has been several online discussions (for instance, http://tinyurl.com/67yovl) and I believe the consensus now is that it should work as expected.

Subject: Re: CLR methods
Posted by: Anith Sen (not signed in)
Posted on: Friday, August 01, 2008 at 10:18 AM
Message: Hello Adam,

That is an excellent point. Rather than using REPLACE function, it is better to have the value method of the xml type. One issue seems to be, as you said, the "annoying games" with multiple levels of derivation. I am not completed sure how to proceed when there is a grouping column; Do you include the grouped column as an element in your xml value like:

SELECT
categoryid, Products
  
FROM ( SELECT p1.CategoryId,
       (
SELECT ProductName + ','
          
FROM Northwind.dbo.Products p2
          
WHERE p2.CategoryId = p1.CategoryId
          
ORDER BY ProductName
            
FOR XML PATH(''), TYPE, ELEMENTS,
      
ROOT ('productname') )  AS Products
      
FROM Northwind.dbo.Products p1
      
GROUP BY CategoryId ) AS list FOR XML RAW, TYPE

Thanks

Subject: grouping
Posted by: Adam Machanic (view profile)
Posted on: Friday, August 01, 2008 at 1:41 PM
Message: Agreed, grouping is a problem, but not just with XML -- virtually all of these solutions (except the cursor and CLR UDA) have quite a bit of unnecessary overhead because the engine doesn't support this stuff.

Regarding cursors, yet another option is a CLR stored procedure. You can read the data with SqlDataReader, do the concatenation with StringBuilder, and send all of the results back to the caller in a single rowset rather than stuffing them into a temp table. Much, much more efficient than a T-SQL cursor.

Subject: Good article
Posted by: PedroJ (not signed in)
Posted on: Saturday, August 02, 2008 at 12:19 AM
Message: This is a useful article. Some methods are very new to me.

Subject: Great article
Posted by: Anonymous (not signed in)
Posted on: Thursday, August 07, 2008 at 2:28 AM
Message: Recursive CTE methods is bit hard to understand

Subject: Here is another way of doing it
Posted by: Phil Factor (view profile)
Posted on: Thursday, August 07, 2008 at 8:56 AM
Message:

This method works in SQL Server 2000 upwards. As you'll see, it goes about it quite a different, slightly cheating, way. As far as I know, Robyn and I invented it, though it took three cups of coffee.


DECLARE @list VARCHAR(MAX)
SELECT @List=COALESCE(@list+',','')
   +
'|'+CONVERT(VARCHAR(5),CategoryID)
   +
'|'+ productName+'|'+CONVERT(VARCHAR(5),CategoryID) +'|'
  
FROM northwind..products ORDER BY categoryID,productName

SELECT CATEGORYid,
  
[Product List]=LEFT(members,CHARINDEX(',|',members+',|')-1) FROM
(
SELECT categoryID, 'members'=
        
REPLACE(
          
SUBSTRING(list,
            
CHARINDEX('|'+CONVERT(VARCHAR(5),CategoryID)+'|',list),8000),
          
'|'+CONVERT(VARCHAR(5),CategoryID)+'|', '')
FROM (SELECT 'list'=@List)f
CROSS JOIN (
  
SELECT categoryID FROM northwind..products GROUP BY categoryID
  
)Categories
)g

Subject: another approach...
Posted by: rudy (not signed in)
Posted on: Thursday, August 07, 2008 at 10:31 AM
Message: [code]SELECT CategoryId
, GROUP_CONCAT(ProductName) AS ProductList
FROM Northwind..Products
GROUP
BY CategoryId[/code]

oh, wait, this how you do it in MySQL

stupid open sores databases, making things easy!!!

;o)

Subject: Re: another approach...
Posted by: Adam Machanic (view profile)
Posted on: Thursday, August 07, 2008 at 2:40 PM
Message: Open sores? Sounds like a product I'd rather avoid.

Subject: Excellent Article
Posted by: TimothyAWiseman (not signed in)
Posted on: Thursday, August 07, 2008 at 4:52 PM
Message: Excellent article and it had some techniques I had never considered before.

Jeff Moden approached the same topic from a purely performance standpoint in: http://www.sqlservercentral.com/articles/Test+Data/61572/

And some of the CTE portion is similar to http://www.sqlservercentral.com/articles/CTE/62404/ though that focuses on readability.

Anith, your article was extremely thorough and enlightening.

Subject: Re: Here is another way of doing it
Posted by: Anith Sen (not signed in)
Posted on: Monday, August 11, 2008 at 1:03 PM
Message: That is a new one, Phil. And a good one:-) I see you build the "big" list and then split it up.

Question, what happens if I change the ORDER BY clause as the following and why?

ORDER BY LEFT( categoryID, 5 ) , productName

Subject: Re: Excellent Article
Posted by: Anith Sen (not signed in)
Posted on: Monday, August 11, 2008 at 1:10 PM
Message: Thanks for the links, Timothy. And for those comments as well :-)

Subject: Re: : Here is another way of doing it
Posted by: Phil Factor (view profile)
Posted on: Monday, August 11, 2008 at 2:01 PM
Message: Simple concatenation isn't always reliable. There are circumstances where it simply doesn't happen. This can vary from one release of SQL Server to another. I sometimes wish they'd tie it down! In the case you mention, there is no supported order for the concatenation to occur. The use of an expression in the ORDER BY forces a different execution plan which produces an indeterminate order for the concatenation, presumably because the order-by is postponed until after the concatenation is performed.

I was brought up to believe that the 'order by' parameter should always be one of those in the result set. I'm not sure if that would help if the order by parameter was the result of a function.

My own experience is that a simple SQL-92 query will result in a correct concatenation. The 'Sybase' update will also usually work reliably, but occasionally needs a 'tickle'. If you pile in with the fancy stuff, you run a risk that the concatenation ends up with merely the last string added to it.

Thanks for a fascinating article. It has certainly taught me several things.

Subject: Excellent article
Posted by: Prashanth Kumar (not signed in)
Posted on: Tuesday, August 12, 2008 at 4:16 AM
Message: This is an excellent article.

Subject: Join and Split
Posted by: mjswart (view profile)
Posted on: Wednesday, August 13, 2008 at 8:18 AM
Message: A very thorough article on Joining/Concatenating strings.

http://dbwhisperer.blogspot.com/2008/06/splitting-and-joining-strings-in-sql.html mentions joining (using xml path syntax) and it also talks about the inverse task of splitting.

Splitting a comma-separated-values is a common task I think. Hopefully it will become less common with table valued parameters.

Subject: re: Join and split
Posted by: Phil Factor (view profile)
Posted on: Wednesday, August 13, 2008 at 9:10 AM
Message: http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/ might give you a few more ideas on the topic of splitting and joining strings. This article really is more about the problem of producing a list of distinguishing columns of all rows that are grouped together by each unique value in a 'Group by' in SQL Server, using concatenation. I've heard it called 'The indexers' grouping.

Subject: join
Posted by: Anonymous (not signed in)
Posted on: Friday, August 22, 2008 at 5:25 AM
Message: ALTER function [dbo].[GetBillStandardFeatures] (
@UnitID int,
@IsStandard bit
)
returns varchar(6000)
as
begin

declare @str nvarchar(4000)
declare @nm varchar(50)
declare @temp varchar(100)
set @str =''

DECLARE RuleList CURSOR FOR
select FeatureID from tblUnitFeature where UnitID = @UnitID
OPEN RuleList

FETCH NEXT FROM RuleList into @nm
WHILE (@@FETCH_STATUS = 0)
begin
set @temp =' '
select @temp = Name from tblFeature where FeatureID in (@nm) and IsStandard = @IsStandard
if @temp <> ' '
begin
set @str = @str + @temp + ' <br/> '
end
FETCH NEXT FROM RuleList into @nm
end
CLOSE RuleList
DEALLOCATE RuleList

return @str
end

i am using this way in this i pass the id from the out side and they join with "br" and return the string

vipul
Cybercom creation (Ahemdabad)
dongavipul@gmail.com
http://dongavipul.blogspot.com

Subject: Recursion and row pairing
Posted by: Cary Hower (not signed in)
Posted on: Friday, August 22, 2008 at 9:20 AM
Message: declare @t table
( CatID tinyint identity(1,1) primary key
, CategoryID tinyint not null
, ProductName varchar(4000) not null
)

declare @Incr tinyint
, @Rows tinyint

insert into @t (CategoryID, ProductName)
select CategoryID
, ProductName
from Products
order by CategoryID
, ProductName

select @Rows = @@RowCount
select @Incr = 1

while @Rows > 0
begin

update l
set ProductName = l.ProductName + ',' + r.ProductName
from @t l
join @t r
on l.CategoryID = r.CategoryID
and l.CatID + @Incr = r.CatID
and (l.CatID - (select min(CatID) from @t where CategoryID = l.CategoryID)) % (@Incr * 2) = 0

select @Rows = @@RowCount

delete l
from @t l
where (CatID - (select min(CatID) from @t where CategoryID = l.CategoryID)) % (@Incr * 2) = case @Incr when 1 then 1 else @Incr end

select @Incr = @Incr * 2
end

--A function can be created that returns @t without the identity column

Subject: Recursion and row pairing (correction)
Posted by: Cary Hower (not signed in)
Posted on: Friday, August 22, 2008 at 9:23 AM
Message: I apologize for an oversight. The delete statment case was unnecessary. "case @Incr when 1 then 1 else @Incr end" should have been simply "@Incr"

Subject: Recursion and row pairing (correction)
Posted by: Cary Hower (not signed in)
Posted on: Friday, August 22, 2008 at 9:25 AM
Message: I apologize for an oversight. The delete statment case was unnecessary. "case @Incr when 1 then 1 else @Incr end" should have been simply "@Incr"

Subject: Newbie's consideration
Posted by: Marzio Alunni (view profile)
Posted on: Friday, August 22, 2008 at 5:25 PM
Message: Hi,I have little experience with SQL server and I know almost nothing about CLR but reading this article I noticed that only the CLR method let encapsulate the concatenation logic in a separate function.
It seems to me a big pro.
The resulting query have far more grater readability, and avoid the overhead imposed by the other methods.
I'm only concerned about performance... You think this is unmotivated, and due to my ignorance of the CLR subject?

Thanks (I Apologize for my poor English... ;P)

Subject: Shamas saeed Considration
Posted by: Shamas saeed (not signed in)
Posted on: Sunday, August 24, 2008 at 11:11 PM
Message: This is good article. You can show row values in one column using , seperated or any other character. This is good for extra knowledge but one thing must mention the we can concate these values in another way and i am doing this from year's before.
We can use coleasce function to do this as.

Select @vProductName = COALESCE(@vProductName +',','') + ProductName from Product

Declare @vProductName a variable and return select result in , seperated values.

Subject: Re:Newbie's consideration
Posted by: Anith Sen (not signed in)
Posted on: Monday, August 25, 2008 at 1:11 PM
Message: Marzio,
As far as separating the concatenation logic, t-SQL UDFs should do it just fine. I am not familiar with the performance factors related to CLR. Perhaps, someone else may chime in,

Anith

Subject: Re: Shamas saeed Considration
Posted by: Anith Sen (not signed in)
Posted on: Monday, August 25, 2008 at 1:15 PM
Message: Shamas,
The method you suggested has some issues -- it can be unpredictable even in some cases, doesn't return any rows. I mentioned it in the article under the section "Unreliable approaches". You can find several examples of failure discussed in the archives of google groups.

Anith

Subject: Re: Re: Newbie's consideration
Posted by: Marzio Alunni (view profile)
Posted on: Wednesday, August 27, 2008 at 5:58 AM
Message: Sorry, I was not clear.
When I wrote "only the CLR method let encapsulate the concatenation logic in a separate function", I meant in a way could by easily reused for every query that need concatenation.
As we can see from your examples, with UDF I can encapsulate concatenation logic for a specific column of a specific table, so if I need to concatenate another column from another table I have to write another UDF.
Do you think its a good argument to prefer CLR aggregate on other options?

Marzio

Subject: Re: Re: Newbie's consideration
Posted by: Marzio Alunni (view profile)
Posted on: Wednesday, August 27, 2008 at 5:59 AM
Message: Sorry, I was not clear.
When I wrote "only the CLR method let encapsulate the concatenation logic in a separate function", I meant in a way could by easily reused for every query that need concatenation.
As we can see from your examples, with UDF I can encapsulate concatenation logic for a specific column of a specific table, so if I need to concatenate another column from another table I have to write another UDF.
Do you think its a good argument to prefer CLR aggregate on other options?

Marzio

Subject: new solution
Posted by: niko_san (not signed in)
Posted on: Tuesday, September 02, 2008 at 7:43 AM
Message: SELECT CategoryId,CAST(ProductName as nvarchar(max)) as ProductName into #Products from Northwind..Products
order by CategoryId,ProductName

declare @StringID nvarchar(max)
declare @StringData nvarchar(max)
SET @StringData = ''
SET @StringID = ''

UPDATE #Products
SET
@StringData = CASE WHEN CategoryId = @StringID
THEN @StringData + ProductName+','
ELSE ProductName+',' END,
@StringID = CategoryId,
ProductName = CASE WHEN CategoryId = @StringID
THEN @StringData
ELSE ProductName END
select CategoryId,substring(max(ProductName),1,len(max(ProductName))-1) from #Products
group by CategoryId
drop table #Products

Subject: new solution
Posted by: niko_san (not signed in)
Posted on: Tuesday, September 02, 2008 at 8:19 AM
Message: SELECT CategoryId,CAST(ProductName as nvarchar(max)) as ProductName into #Products from Northwind..Products
order by CategoryId,ProductName

declare @StringID nvarchar(max)
declare @StringData nvarchar(max)
SET @StringData = ''
SET @StringID = ''

UPDATE #Products
SET
@StringData = CASE WHEN CategoryId = @StringID
THEN @StringData + ProductName+','
ELSE ProductName+',' END,
@StringID = CategoryId,
ProductName = CASE WHEN CategoryId = @StringID
THEN @StringData
ELSE ProductName END
select CategoryId,substring(max(ProductName),1,len(max(ProductName))-1) from #Products
group by CategoryId
drop table #Products

Subject: sybase list
Posted by: jengo (not signed in)
Posted on: Thursday, September 18, 2008 at 7:25 PM
Message: table:
id name
-----------
1 test
2 again
1 work
2 better

sybase sql string:
select id, list(name) from table_name group by id

result:
id list(name)
-----------------
1 test ,work
2 again ,better

Subject: Good one
Posted by: Murali (view profile)
Posted on: Tuesday, October 28, 2008 at 3:54 PM
Message: I have learnt lot of things from this artical.

Subject: length limit
Posted by: Chi (not signed in)
Posted on: Thursday, October 30, 2008 at 2:49 PM
Message: It works well and is an elegant solution, but when the result is more 255, the rest is cut off. Any idea how to work around this?

Thanks!

Subject: Re: length limit
Posted by: Chi (not signed in)
Posted on: Thursday, October 30, 2008 at 2:52 PM
Message: (I was referring to the For XML Path approach)

Subject: Anonymous Commenting Disabled
Posted by: Chris Massey (view profile)
Posted on: Monday, November 10, 2008 at 5:53 AM
Message: Anonymous commenting has been disabled in this article due to spamming. If you want to add your comments, I'm afraid you'll either need to to sign in or sign up. Sorry for the inconvenience.

Subject: Kick Me
Posted by: Lee (view profile)
Posted on: Thursday, December 18, 2008 at 12:06 PM
Message: Fine, I'll paint the target on my T-shirt on this one: I have been using the "unreliable" string concatenation technique -- "Scalar UDF with variable concatenation in SELECT" -- since I started out doing Sybase in 1991. Years before, I might add, people began having heart murmurs over this technique. Over and over, I have been lectured on its "unreliability", but in seventeen years, I have never seen it break.

But... point taken. It is a good policy not to release code that relies on an unsupported behavior. I do not think it would be easy for Microsoft to break that particular behavior -- they would have to change or take away altogether the ability to set a VARCHAR local variable with a SELECT, and physically do something to keep the implied loop in the SELECT from running. But that's really beside the point. Thanks for a good article.

Subject: CLR Error
Posted by: DBowlerHB (view profile)
Posted on: Wednesday, February 25, 2009 at 12:58 PM
Message: Great artical Anith!! I really liked the idea of using CLR to add this function so it could be reused easily. I tried to create the new strConcat function using your example word for word unfortunatly I'm getting an error when I build the solution.

On the line:
"private System.Collections.Generic.List values;"
The word "List" is underlined with the following error:
"Using the gerneric type 'System.Collections.Gerneric.List<T>' requires '1' type arguments."

Because I am very new to C# I do not know how to fix this issue, and thought I would notify you that your sample may have a problem.

Any ideas?

~DB

Subject: String Concatenation Challenge/Contest
Posted by: Adam Machanic (view profile)
Posted on: Friday, February 27, 2009 at 12:31 PM
Message: Enjoyed this article?

Good, now go try to solve a T-SQL string concatenation challenge and win a prize:

http://sqlblog.com/blogs/adam_machanic/archive/2009/02/27/t-sql-challenge-grouped-string-concatenation.aspx

:-)

Subject: Re: Shamas saeed Considration
Posted by: Dennis (view profile)
Posted on: Sunday, March 01, 2009 at 1:44 PM
Message: Like Lee, I use this method a lot. I accept that the order of items in the concatenated list is not reliable. It doesn't even make sense to Order By on a column that couldn't logically be present in the result set.

Aside from that, I don't understand any purported dependency on physical implementation, indexing or the like.

Can anybody explain that or give a test case where the concatenation method doesn't work?


Subject: Actually, variable concatenation in SELECT is Reliable
Posted by: RBarryYoung (view profile)
Posted on: Sunday, March 01, 2009 at 1:47 PM
Message: For SQL Server 2005 at least, it is not correct to say that variable concatenation in SELECT is either unreliable or dependent on the physical implementation. The example given is unreliable because it has an error in that it lacks an ORDER BY clause to insure the proper ordering. Here is a correct implementation:

CREATE FUNCTION dbo.udf_select_concat ( @c INT )
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
SET @p = '' ;
SELECT @p = @p + ProductName + ','
FROM Northwind..Products
WHERE CategoryId = @c
ORDER BY ProductName ;
RETURN @p
END

This is all documented and supported straight out of BOL. Finally, the referenced knowledgebase article is not relevant because it is only for SQL Server 2000 and 7.0.

Subject: Re: Shamas saeed Considration
Posted by: Dennis (view profile)
Posted on: Sunday, March 01, 2009 at 6:32 PM
Message: Like Lee, I use this method a lot. I accept that the order of items in the concatenated list is not reliable. It doesn't even make sense to Order By on a column that couldn't logically be present in the result set.

Aside from that, I don't understand any purported dependency on physical implementation, indexing or the like.

Can anybody explain that or give a test case where the concatenation method doesn't work?


Subject: Variable concatenation in SELECT
Posted by: Anith (view profile)
Posted on: Wednesday, April 01, 2009 at 3:26 PM
Message: RBarryYoung and Dennis,

There are several cases where it fails. Several forum members in Microsoft newsgroups have posted several examples, at least from version 7.0 onwards. For some examples, see:

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/70917b5ca789ea77?dmode=source

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/371167d94545e1d7?hl=en&dmode=source

The problem is several people ignore these warnings and keep using them only to find one day it suddenly stopped working!

Anith

Subject: Much Simpler Way
Posted by: Stephan Koch (view profile)
Posted on: Monday, July 13, 2009 at 4:41 PM
Message: Hi Anith

This is how I do it. Not sure why nobody else sems to do it this way:

-- Initialise variables
DECLARE @values varchar(MAX)
set @values = ''
-- Get the string
Select @values = @values + '[' + ProductName + '],' from Northwind..products
-- Strip out the last comma
select @values = left(@values, len(@values)-1)
Print @values

This code is not entirely complete, since it will fall over when there are no rows in the product table, but you get the idea.

I use this approach to build values for variable PIVOT clauses.

Subject: Much simpler way part 2
Posted by: Stephan Koch (view profile)
Posted on: Monday, July 13, 2009 at 5:10 PM
Message: Oops - just realised that I was solving a different problem - mine only works for a single group at a time... sorry for confusion.

Subject: Much simpler way part 2
Posted by: Stephan Koch (view profile)
Posted on: Monday, July 13, 2009 at 5:50 PM
Message: Oops - just realised that I was solving a different problem - mine only works for a single group at a time... sorry for confusion.

Subject: Anub Philip's method
Posted by: GeoffS (view profile)
Posted on: Monday, August 03, 2009 at 7:40 AM
Message: Hi, All, first time on this site for me and very, very useful.

I just want to add my positive comment on the method of Anub Philip - for my application, this runs in less than a second compared to ~2:35:00 (that's 2 minutes 35 sec) for exactly the same result using the previous CTE method.

The pre-ranking of the records for the CTE was a huge benefit.

The slower method did run quite fast on a simpler data set, but sank on the more complex table.

Geoff

Subject: i have a problem .. pls solve it
Posted by: suresh123 (view profile)
Posted on: Wednesday, August 05, 2009 at 7:16 AM
Message: Actually i have to run this method on huge table of around 65000 rows, and the column to be concatenated is ntext field , i cannot change to varchar because of data lose...
when i use this "Scalar UDF with t-SQL update extension"
My notes get truncated ...
Any other method to do my job

Subject: How do I add new lines instead of commas?
Posted by: PeggyScott84 (view profile)
Posted on: Wednesday, September 16, 2009 at 1:46 PM
Message: I am trying to concatenate row values with newlines in stead.
I need to display the query results on a ColdFusion 8 website.

It sounds basic but I have tried using char(10) char(13)

\n, \n\r, \r

tried using <br>, </br>

nothing works.

Any help/suggestion is welcome!
Thanks!

Subject: Common Language Runtime - Error In Solution
Posted by: BugMeNot (view profile)
Posted on: Thursday, November 19, 2009 at 10:35 PM
Message: The code in the "Using Common Language Runtime" section of this article results in the following error:

Using the generic type 'System.Collections.Generic.List<T>' requires '1' type arguments

To correct this error, replace all references to "List" with "List<string>".

Subject: Very interesting article..
Posted by: KLynch0803 (view profile)
Posted on: Monday, February 01, 2010 at 3:52 PM
Message: I love this article problem is I dont understand it completely.. I'm far from a programmer but I'm trying to manipulate some data I have to insert into a form im updating values in. Can someone please help me edit this process to wrok in MSAccess?

I have the following data with multiple City and state Values that are equal but unique zipcodes. This list is 43,000 rows long:

tbl_City_State
City StateName ZipCode

I need an output like such for each City state without duplicate city state names...

Raleigh NC 29706 29707 29708

Meaning if there are 3 or 50 Raleigh NC in the table it will make one and add all the zip codes to the one cell with the city state name.

I would appreciatte any help in advance I have been trying to figure out how to do this in excel or access for about 2 weeks now.

Subject: Very interesting article..
Posted by: KLynch0803 (view profile)
Posted on: Monday, February 01, 2010 at 9:35 PM
Message: I love this article problem is I dont understand it completely.. I'm far from a programmer but I'm trying to manipulate some data I have to insert into a form im updating values in. Can someone please help me edit this process to wrok in MSAccess?

I have the following data with multiple City and state Values that are equal but unique zipcodes. This list is 43,000 rows long:

tbl_City_State
City StateName ZipCode

I need an output like such for each City state without duplicate city state names...

Raleigh NC 29706 29707 29708

Meaning if there are 3 or 50 Raleigh NC in the table it will make one and add all the zip codes to the one cell with the city state name.

I would appreciatte any help in advance I have been trying to figure out how to do this in excel or access for about 2 weeks now.

Subject: Very Good Men.
Posted by: melhorum (view profile)
Posted on: Thursday, February 11, 2010 at 9:08 AM
Message: it's clean code,thanks.

Subject: Simpler....
Posted by: dmc (view profile)
Posted on: Wednesday, June 02, 2010 at 4:59 PM
Message: Why not this solution??

Problem: To show all the cities as comma separated list for each region

use northwind

--raw data...
select region,city from customers where len(region)=2 order by 1,2

region city
--------------- ---------------
AK Anchorage
BC Tsawassen
BC Vancouver
CA San Francisco
DF Caracas
ID Boise
MT Butte
NM Albuquerque
OR Elgin
OR Eugene
OR Portland
OR Portland
RJ Rio de Janeiro
RJ Rio de Janeiro
RJ Rio de Janeiro
SP Campinas
SP Resende
SP Sao Paulo
SP Sao Paulo
SP Sao Paulo
SP Sao Paulo
WA Kirkland
WA Seattle
WA Walla Walla
WY Lander

(25 row(s) affected)

-- cities within each region
select
region,
(select stuff((select distinct ',' + city as [text()]
from customers
where region=c.region
order by ',' + city
for xml path('')),1,1,'')) as 'cities'

from customers c
where len(region)=2 --limiting view
group by region


region cities
--------------- -------------------------------
AK Anchorage
BC Tsawassen,Vancouver
CA San Francisco
DF Caracas
ID Boise
MT Butte
NM Albuquerque
OR Elgin,Eugene,Portland
RJ Rio de Janeiro
SP Campinas,Resende,Sao Paulo
WA Kirkland,Seattle,Walla Walla
WY Lander

(12 row(s) affected)

no functions, cursors, declares, etc... am I missing something??

Subject: Re: Why not this solution??
Posted by: Anith (view profile)
Posted on: Friday, June 25, 2010 at 4:56 PM
Message: dmc,

No, you are not missing anything. It is already addressed in the article under the section "The blackbox XML methods".

Anith Larson

Subject: Great examples
Posted by: dmigliore (view profile)
Posted on: Thursday, August 05, 2010 at 10:09 AM
Message: Thanks for the examples of the various methods. This gives me more choices in how to solve the concatenation problem.

Subject: Concat using XQuery
Posted by: originator (view profile)
Posted on: Thursday, October 28, 2010 at 5:01 AM
Message: Hi guys!

I'd like to mention another one fast, XML-based and "entity"-safe method:

declare @iXml xml;

SELECT @iXml = (
SELECT ProductName + ','
FROM Northwind.dbo.Products
FOR XML PATH);

select @iXml.value('string(/)','nvarchar(max)');

Good luck!

Subject: Migrating from Oracle to SQL Server - Aggregate function
Posted by: kalpesh (view profile)
Posted on: Monday, February 07, 2011 at 12:22 AM
Message: Hi, Need help in converting the StrAgg (ODCIAggregate from Oracle) function (http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dciaggfns.htm#BEJBEBHE) to SQL Server compatible.

There are many Views which use this function in generic way. Don't want to do it in C# so as to keep in SQL specific.

Select
Docid,
Protocol,
StrAgg(Jan) as 'Jan',
StrAgg(Feb) as 'Feb',
StrAgg(Mar) as 'Mar',
StrAgg(Apr) as 'Apr',
StrAgg(May) as 'May',
StrAgg(Jun) as 'Jun',
StrAgg(Jul) as 'Jul',
StrAgg(Aug) as 'Aug',
StrAgg(Sep) as 'Sep',
StrAgg(Oct) as 'Oct',
StrAgg(Nov) as 'Nov',
StrAgg(Dec) as 'Dec'
From
(
Select
Docid,
P.Protocol,
Case When To_Char(PL.PamDt,'Mon') = 'Jan' Then To_Char(PL.ActDt,'DD-Mon') ELSE NULL END As Jan,
Case When To_Char(PL.PamDt,'Mon') = 'Feb' Then To_Char(PL.ActDt,'DD-Mon') ELSE NULL END As Feb,
Case When To_Char(PL.PamDt,'Mon') = 'Mar' Then To_Char(PL.ActDt,'DD-Mon') ELSE NULL END As Mar,
...... Dec
From
(
Select
T.DocId,
T.Protocol
From
PAMSTR T,
CITY Cy
Where Cy.CNum <= T.CycleNum
) P,
PAMSTRDTL Pl
Where
P.DocId = Pl.DocId (+)
And P.CNum = Pl.ActCNum (+)
Order By CNum,PamDt
)
Group by DocId, Protocol


If I execute without Aggregate I get:
Docid Protocol Jan Feb Mar
1 P1 01-Jan
1 P1 02-Jan 02-Mar
2 P2 02-Mar
2 P2 01-Jan 05-Mar


If I execute with Aggregate I get:
Docid Protocol Jan Feb Mar
1 P1 01-Jan, 02-Jan 02-Mar
2 P2 01-Jan 02-Mar, 05-Mar



Any guidance appreciated to get generic solution.

Subject: Peeling back
Posted by: ISAKOS (view profile)
Posted on: Wednesday, June 08, 2011 at 8:00 AM
Message: Great article,
it solved my issue,
I would like to say thank to all of you,
regarding to Anith Sen




Subject: create an index view using these queries
Posted by: multispective (view profile)
Posted on: Wednesday, September 18, 2013 at 11:56 PM
Message: good explanations

since I want to join the csv results against other tables, i would like to create indexed views using the queries described in this article.
i tried to create indexed views using the two query techniques in the article: CTE and XML.
But sql server prevents me from creating an index on views that use CTE and subquery.
this is for a large table so are there any other fast ways to be able to have the csv results?
thanks

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.