Login or Sign Up to become a member!

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.

Your profile

Search

Views

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

tsqlLine number Off | Hide | Select all

CREATE TABLE SomeTable (ID INT,SomeValue VARCHAR(30))
INSERT SomeTable VALUES(1,'wasabi')
INSERT SomeTable VALUES(1,'cheese')
INSERT SomeTable VALUES(1,'sushi')
INSERT SomeTable VALUES(1,'kimchi')
INSERT SomeTable VALUES(2,'eel')
INSERT SomeTable VALUES(2,'monkfish')
INSERT SomeTable VALUES(2,'seabass')
INSERT SomeTable VALUES(3,'mackerel')
INSERT SomeTable VALUES(3,'shark')

Then create this function

tsqlLine number Off | Hide | Select all

CREATE FUNCTION fnConcatenate (@id INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @v VARCHAR(8000)
SELECT @v =''
SELECT @v =@v + ISNULL(SomeValue,'NULL') +','
FROM SomeTable
WHERE ID = @id
SELECT @v = LEFT(@v,(LEN(@v)-1))
RETURN @V
END

You can call the function for each id by itself

tsqlLine number Off | Hide | Select all

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

You can also use the function on the whole table

tsqlLine number Off | Hide | Select all

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

How does this work?

First we create the variables and we initialize to a blank

tsqlLine number Off | Hide | Select all

DECLARE @v VARCHAR(8000)
SELECT @v =''

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

tsqlLine number Off | Hide | Select all

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

Finally we get rid of the last comma

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

INSERT SomeTable VALUES(4,'mackerel')
INSERT SomeTable VALUES(4,'shark')
INSERT SomeTable VALUES(4,null)
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

Currently 2.33/5
1
2
3
4
5

132 Rating: 2.3/5 (12 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Concatenate_Values_From_Multiple_Rows_Into_One_Column"