How to create a comma delimited list using SQL Server

By Evan Schmidt

This mini-blog describes a quick method to create a comma separated field within a SQL query using SQL Server.

 

In this example there are two teams with multiple sponsors for each team.

TeamID	TeamName
1	Ducks
2	Bears

 

TeamID	SponsorName
1	Fuller Co.
1	Martin & Chase
2	Bo Thomas Inc

 

You would like the query to return a delimited list that contains the sponsors for each team.

TeamID	TeamName	SponsorList
1	Ducks	Fuller Co.; Martin & Chase
2	Bears	Bo Thomas Inc

 

 

The following query will takes the first two tables as inputs and creates the third table. Please note that the “FOR XML PATH” statement will cause xml restricted characters to be replaced.

 

SELECT

L.TeamID

, L.TeamName

—replace xml friendly ‘&’ with ‘&’

, REPLACE(

   —limit the final output to 100 characters

   CONVERT(NVARCHAR(100),

          —the first 3 characters of this new field are removed

          SUBSTRING(

                (

                —the xml output is created which creates the delimited list

SELECT ( ‘; ‘ + TMP.SponsorName) –requires no column name

                FROM (

                       —sponsor list

                       SELECT 1 as TeamID, ‘Fuller Co.’ as SponsorName

                        UNION ALL

                       SELECT 1 as TeamID, ‘Martin & Chase’ as SponsorName

                       UNION ALL

                       SELECT 2 as TeamID, ‘Bo Thomas Inc’ as SponsorName

                ) TMP

                WHERE TeamID = L.TeamID

                FOR XML PATH(”) —required empty xml path

 

          ), 3, 100)

   )

,‘&’,‘&’) AS SponsorList

  

FROM (

                —team list

                SELECT 1 as TeamID, ‘Ducks’ as TeamName

                UNION ALL

                SELECT 2 as TeamID, ‘Bears’ as TeamName

) L

 

How to create an auto generated row count within a query using SQL Server

By Evan Schmidt

This mini-blog describes how to dynamically generate an ID column within a SQL Query using SQL Server 2008.

 

We will start with a simple table called “Table1”:

Column1

Yes

No

Maybe

 

 

We want to create an Auto Generated ID for each row returned from the table. For Example:

Column1	AutoGeneratedID
Maybe	1
No	2
Yes	3

 

 

The following SQL Statement can be used to generate this identity column:

select Column1

, ROW_NUMBER() over (order by Column1) as AutoGeneratedID

from dbo.Table1

 

Note how the row count is assigned based on the ORDER BY clause specified within the OVER statement.