Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Concatenate Values From Multiple Rows Into One Column

From Wiki

Jump to: navigation, search

You have a table and you need to concatenate the values from multiple rows into one row. For exaple if this is your table

1 wasabi
1 cheese
1 sushi
1 kimchi
2 eel
2 monkfish
2 seabass
3 mackerel
3 shark


you want this as an output

1 wasabi,cheese,sushi,kimchi
2 eel,monkfish,seabass
3 mackerel,shark


How can you do this? Let's take a look.

First create this table

  1. CREATE TABLE SomeTable (ID INT,SomeValue VARCHAR(30))
  2.     INSERT SomeTable VALUES(1,'wasabi')
  3.     INSERT SomeTable VALUES(1,'cheese')
  4.     INSERT SomeTable VALUES(1,'sushi')
  5.     INSERT SomeTable VALUES(1,'kimchi')
  6.     INSERT SomeTable VALUES(2,'eel')
  7.     INSERT SomeTable VALUES(2,'monkfish')
  8.     INSERT SomeTable VALUES(2,'seabass')
  9.     INSERT SomeTable VALUES(3,'mackerel')
  10.     INSERT SomeTable VALUES(3,'shark')


Then create this function

  1. CREATE FUNCTION fnConcatenate  (@id INT)
  2.     RETURNS VARCHAR(8000)
  3.     AS
  4.     BEGIN
  5.      
  6.     DECLARE @v VARCHAR(8000)
  7.     SELECT @v =''
  8.      
  9.     SELECT @v =@v + ISNULL(SomeValue,'NULL')  +','
  10.     FROM SomeTable
  11.     WHERE ID = @id
  12.      
  13.     SELECT @v = LEFT(@v,(LEN(@v)-1))
  14.     RETURN @V
  15.     END

You can call the function for each id by itself

  1. SELECT dbo.fnConcatenate(1)
  2.     SELECT dbo.fnConcatenate(2)
  3.     SELECT dbo.fnConcatenate(3)

You can also use the function on the whole table

  1. SELECT DISTINCT id ,dbo.fnConcatenate(id)
  2.     FROM SomeTable


How does this work?


First we create the variables and we initialize to a blank

  1. DECLARE @v VARCHAR(8000)
  2.     SELECT @v =''

I replaced NULL values with 'NULL' so that we know we have NULL values, you can use blanks, spaces or 'N/A'

  1. SELECT @v =@v + ISNULL(SomeValue,'NULL')  +','
  2.     FROM SomeTable
  3.     WHERE ID = @id


Finally we get rid of the last comma

  1. SELECT @v = LEFT(@v,(LEN(@v)-1))

so let's insert a bunch of new rows and one of them will have a NULL values

  1. INSERT SomeTable VALUES(4,'mackerel')
  2.     INSERT SomeTable VALUES(4,'shark')
  3.     INSERT SomeTable VALUES(4,null)
  4.  
  5.     SELECT dbo.fnConcatenate(4)

Output

mackerel,shark,NULL


Contributed by: --SQLDenis 02:32, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Transposing and pivoting

132 Rating: 2.6/5 (80 votes cast)