//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: General SQL Server & Access Articles :: How do I concatenate strings from a column into a single row?


How do I concatenate strings from a column into a single row?

This question is asked quite frequently. People want to take a column like this: 
 
Color 
------ 
red 
orange 
blue 
green
 
And return a resultset like this: 
 
Colors 
------------------------- 
red,orange,blue,green
 
This isn't exactly relational, and can certainly be handled by the presentation layer. However, there are kludges in SQL Server that will allow you to derive a solution. We'll start with the simple case above: 
 
CREATE TABLE Colors 

    Color VARCHAR(32) 

GO 
 
SET NOCOUNT ON 
INSERT Colors SELECT 'red' 
INSERT Colors SELECT 'orange' 
INSERT Colors SELECT 'blue' 
INSERT Colors SELECT 'green' 
GO 
 
DECLARE @colors VARCHAR(1024) 
 
SELECT 
    @colors = COALESCE(@colors + ',', '') + Color 
FROM 
    Colors 
 
SELECT Colors = @colors 
GO 
 
DROP TABLE Colors 
GO
 
(Note that you can add an ORDER BY clause to the SELECT, but this will not necessarily be obeyed... the resulting list will be displayed in an arbitrary order. Please observe the wording thereā€”I'm not saying your ORDER BY will be ignored, just that it's not guaranteed to be obeyed. In most cases it will come back the way you'd expect, but it is quite possible that there are certain scenarios in which it will be arbitrary or, otherwise, not what you expected.) 
 
Now, that's great if you're only concerned with getting one list back. But let's say we have a table of athlete names, events and results. For each athlete, we want a row containing the full name and a comma-separated list of events they medaled in. In this case, we'll use a user-defined function to handle the concatenation for us, and a subquery to limit the output to one row per name. 
 
CREATE TABLE dbo.Medals 

    FullName VARCHAR(32), 
    Result VARCHAR(12), 
    Event VARCHAR(32), 
    PRIMARY KEY (Fullname, Event) 

GO 
 
CREATE FUNCTION dbo.GetMedalResult 

    @fullName VARCHAR(32) 

RETURNS VARCHAR(8000) 
AS 
BEGIN 
    DECLARE @r VARCHAR(8000) 
    SELECT @r = ISNULL(@r+',', '') 
        + Result + ' in ' + Event 
        FROM dbo.Medals 
        WHERE FullName = @fullName 
    RETURN @r 
END 
GO 
 
 
SET NOCOUNT ON 
INSERT Medals SELECT 'Marie-Helene Premont', 'Silver', 'Mountain Biking' 
INSERT Medals SELECT 'Adam van Koeverden', 'Bronze', 'K-1 1,000M Kayak' 
INSERT Medals SELECT 'Kyle Shewfelt', 'Gold', 'Gymnastics Floor Exercise' 
INSERT Medals SELECT 'Alexandre Despatie', 'Silver', '3-Meter Springboard' 
INSERT Medals SELECT 'Michael Phelps', 'Gold', '400M Medley' 
INSERT Medals SELECT 'Michael Phelps', 'Gold', '200M Medley' 
INSERT Medals SELECT 'Michael Phelps', 'Gold', '200M Butterfly' 
INSERT Medals SELECT 'Michael Phelps', 'Gold', '100M Butterfly' 
INSERT Medals SELECT 'Michael Phelps', 'Gold', '4x200M Freestyle Relay' 
INSERT Medals SELECT 'Michael Phelps', 'Bronze', '200M Freestyle' 
INSERT Medals SELECT 'Michael Phelps', 'Bronze', '4x100M Freestyle Relay' 
GO 
 
SELECT 
    Fullname, 
    dbo.getMedalResult(Fullname) 
FROM 

    SELECT 
        Fullname 
    FROM 
        Medals 
    GROUP BY 
        Fullname 
) i 
GO 
 
DROP TABLE Medals 
DROP FUNCTION dbo.getMedalResult 
GO
 
Again, you will not be able to dictate the ordering of the result in each row; this will be decided by the optimizer at runtime, and will not necessarily stay consistent between executions. 
 
For some more thorough examples, please see this thread starring Anith Sen. 
 
SQL Server 2005 adds some interesting options for this kind of non-relational query. You should be able to have more granular control over concatenation with user-defined aggregates, and you can also take advantage of the new FOR XML PATH: 
 
USE AdventureWorks 
GO 
 
SELECT 
    CustomerID, 
    SalesOrderIDs = REPLACE( 
        ( 
            SELECT 
                SalesOrderID AS [data()] 
            FROM 
                Sales.SalesOrderHeader soh 
            WHERE 
                soh.CustomerID = c.CustomerID 
            ORDER BY 
                SalesOrderID 
            FOR XML PATH ('') 
        ), ' ', ',') 
FROM 
    Sales.Customer c 
ORDER BY 
    CustomerID
 
Another way is to use the new CROSS APPLY operator: 
 
USE AdventureWorks 
GO 
 
SELECT 
    CustomerID, 
    SalesOrderIDs = LEFT(o.list, LEN(o.list)-1) 
FROM 
    Sales.Customer c 
CROSS APPLY 

        SELECT 
            CONVERT(VARCHAR(12), SalesOrderID) + ',' AS [text()] 
        FROM 
            Sales.SalesOrderHeader s 
        WHERE 
            s.CustomerID = c.CustomerID 
        ORDER BY 
            SalesOrderID 
        FOR XML PATH('') 
    ) o (list) 
ORDER BY 
    CustomerID

Related Articles

Can I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?
Could I get some help with JOINs?
How can I tell which version of MDAC I'm running?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I change column order in a table structure?
How do I change the order of columns in a table?
How do I convert columns of values into a single list?
How do I determine if a database exists?
How do I document / compare my SQL Server database(s)?
How do I get the IDENTITY / AUTONUMBER value for the row I inserted?
How do I solve 'ADO Could Not Find The Specified Provider'?
Should I use BETWEEN in my database queries?
Why can't I use the * wildcard in a database search?
Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
Why do I get weird results when using both AND and OR in a query?
Why do some SQL strings have an 'N' prefix?
Why does AbsolutePosition return as -1?
Why doesn't SQL Server allow me to separate DATE and TIME?
Why is Query Analyzer only returning 255 characters?
Why should I avoid NULLs in my database?
How do I deal with an apostrophe (') in a SQL statement?

 

 


Created: 8/27/2004 | Last Updated: 7/13/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (230)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...