Appending multiple rows into a single row in T-sql

Question

Hi All,

I have a table like this 

 Code Name  Address

 1 sam   #1, State Highway

 1 sam   Second Cross

 1 sam   highway city

How can i join all the three rows in to a single row like this

 Code     Name    Address

 1           sam        #1, State Highway Second Cross highway city

I have 1000's of records to be formatted like this into a single unique record.  Code is the only unique column in the table

Thanks in advance

  

Answer 1

DECLARE @t TABLE(Id int,Name varchar(50))
INSERT INTO @t
SELECT 1,'#1, State Highway' union all
SELECT 1,'Second Cross' union all
SELECT 1,'highway city' union all
SELECT 2,'#2, State Highway' union all
SELECT 2,'Third Cross' union all
SELECT 2,'highway' union all
SELECT * FROM @t
SELECT ID,
STUFF(
	(
	SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML path('')
	),
	1,
	1,
	''
) 
FROM (SELECT DISTINCT ID FROM @t )t

---

Narsimha

Answer 2

Search in this forum for "FOR XML PATH('')", which is the most common method to do concatenation aggregate.

select T.code, T.name, (select Address as [data()] from T as S where S.code = T.code and S.name = T.name) as Address
from (select distinct code, name from T) as R;

In this specific case you will need a criteria to guarantee the order of the concatenation otherwise the concatenation will be non-deterministic.

---

AMB

Some guidelines for posting questions...

Answer 3

This is not a table and rows are not records. A properly designed table will have columns with one and only kind of data in it; your "<something>_address" column is mixed data. You need to go outside of SQL to assemble a properly defined data element. XML might be one way or (better) you can look for a mailing lsit product that will also put the adddress components into their own columns (street address, city name, state or province code, postal code) and formt them to whatever postal regulations you have to follow. In the US, the standard is CASS and Melissa Data is one product.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL