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.

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.

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
  6.     DECLARE @v VARCHAR(8000)
  7.     SELECT @v =''
  9.     SELECT @v =@v + ISNULL(SomeValue,'NULL')  +','
  10.     FROM SomeTable
  11.     WHERE ID = @id
  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)
  5.     SELECT dbo.fnConcatenate(4)



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

Part of SQL Server Programming Hacks

Section Transposing and pivoting

132 Rating: 2.3/5 (101 votes cast)