Concatenate multiple rows into a single row

Question

Concatenate multiple rows into a single row

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 1

Ah... just a note... if the concatenation exceeds 8000 characters in SQL Server 2000, there's virtually no chance of displaying the full width of the concatenation and we may have to do something else to get the correct return...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 2

You bet... thanks for the feedback, Eric.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

anthony.evans Grasshopper Points: 21 More actions · Answer 3

simple but effective solution in sql 2000 , modified to return each case statement result in different column , effectively creating pivot report.thank you

Raghu Kurella SSC Rookie Points: 32 More actions · Answer 4

This is it man. Awesome. The first solution never finished after 7 min with 11000 records and this solution runs in less than "1" yes ONE second...

Thanks for sharing your knowledge. Doesn't matter if you didn't invent it. You shared it!!!

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 5

anthony.evans (11/15/2007)
simple but effective solution in sql 2000 , modified to return each case statement result in different column , effectively creating pivot report.thank you

If you'd like to see more about this "cross tab" technology in SQL Server 2000, please see the following...

http://www.sqlservercentral.com/articles/T-SQL/63681/

... and thanks for the feedback 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lakki1221 Grasshopper Points: 17 More actions · Answer 6

Thanks for every one.. This topic is really useful for me

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 7

Raghu Kurella (9/10/2008)
This is it man. Awesome. The first solution never finished after 7 min with 11000 records and this solution runs in less than "1" yes ONE second...
Thanks for sharing your knowledge. Doesn't matter if you didn't invent it. You shared it!!!

Thanks for the feedback, Raghu!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ben.reese Old Hand Points: 367 More actions · Answer 8

I appreciate that this thread is quite old so for forgive me for resurrrecting it.

this is great...

I like Eric Mamet's xml solution best - thanks Eric. it has let me to create this generic sproc that will serialise distinct valus from any column of any table into a comma seperated string

CREATE PROCEDURE usp_distinct_values_to_delimited_string(@table_name NVARCHAR(255), @column_name NVARCHAR(255))

AS

BEGIN

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '

SELECT

(

SELECT CAST(t.{column_name} AS NVARCHAR(255)) + '',''

FROM {table_name} t

ORDER BY t.{column_name}

FOR XML PATH('''')

) AS {column_name}_list'

SET @SQL = REPLACE(@SQL,'{table_name}',@table_name)

SET @SQL = REPLACE(@SQL,'{column_name}',@column_name)

EXEC sp_executesql @SQL

END

GO

Enjoy :hehe:

Eric Mamet SSChampion Points: 11728 More actions · Answer 9

Thanks

I thought the "political correctness" about correlated sub-query was forgetting how flexible the XML approach was.

Don't repeat it but I still do that sort of things from time to time!!! 😉