Concatenate multiple rows into one column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenate multiple rows into one column?

Author

Topic

lwhalen618
Starting Member

4 Posts

Posted - 2008-05-07 : 21:07:08

Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:

TableParent (ASSIGNNUM (PK), DESC, STARTDATE)
TableChild (ASSIGNNUM (FK), EMPLOYEENUM)

There could be multiple employees for each assignment. Sample data:

TableParent
1....First Assignment....05/01/2008
2....Second Assignment...05/03/2008
3....Third Assignment....05/07/2008

TableChild
1....55342
2....33456
2....52343
3....35225
3....45121
3....11553

I would like the query result to look like this:

1....First Assignment....05/01/2008....55342
2....Second Assignment...05/03/2008....33456,52343
3....Third Assignment....05/07/2008....35225,45121,11553

Any suggestions would be appreciated!

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-05-08 : 00:04:56

Here are a few examples:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293

Nathan Skerl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 04:08:51

[code]SELECT p.ASSIGNNUM,p.DESC,p.STARTDATE,LEFT(el.EmpList,LEN(el.EmpList)-1)
FROM TableParent p
CROSS APPLY (SELECT EMPLOYEENUM + ',' AS [text()]
FROM TableChild
WHERE ASSIGNNUM =p.ASSIGNNUM
FOR XML PATH(''))el(EmpList)[/code]

lwhalen618
Starting Member

4 Posts

Posted - 2008-05-08 : 22:59:36

Thanks, everyone. I'm working through your suggestions and will report back my solution.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-09 : 04:46:05

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

lwhalen618
Starting Member

4 Posts

Posted - 2008-05-12 : 23:10:27

I used a ConcatOrderProducts function found in the following post to solve my problem. It worked perfectly!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293

Thanks for your help everyone.

Lisa

Gawaine79
Starting Member

6 Posts

Posted - 2009-12-23 : 15:56:31

Anyone looking for another simple way to do with
Works in sql 2005 and later I believe.

CREATE TABLE #TableParent (ASSIGNNUM int primary key, description varchar(512), STARTDATE datetime)
CREATE TABLE #TableChild (ASSIGNNUM int, EMPLOYEENUM int)

/*
There could be multiple employees for each assignment. Sample data:

*/
INSERT INTO #TableParent VALUES ( 1,'First Assignment', '05/01/2008')
INSERT INTO #TableParent VALUES ( 2,'Second Assignment','05/03/2008')
INSERT INTO #TableParent VALUES ( 3,'Third Assignment','05/07/2008')

INSERT INTO #TableChild VALUES (1,55342)
INSERT INTO #TableChild VALUES (2,33456)
INSERT INTO #TableChild VALUES (2,52343)
INSERT INTO #TableChild VALUES (3,35225)
INSERT INTO #TableChild VALUES (3,45121)
INSERT INTO #TableChild VALUES (3,11553)

select p.assignNum, p.description, p.startDate,
stuff((select ', ' + cast(employeeNum as varchar(512))from #TableChild c where c.ASSIGNNUM = p.ASSIGNNUM for xml path('')),1,2,'') as employeeList
from #TableParent p
;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-24 : 01:31:07

quote:
Originally posted by Gawaine79

Anyone looking for another simple way to do with
Works in sql 2005 and later I believe.

CREATE TABLE #TableParent (ASSIGNNUM int primary key, description varchar(512), STARTDATE datetime)
CREATE TABLE #TableChild (ASSIGNNUM int, EMPLOYEENUM int)

/*
There could be multiple employees for each assignment. Sample data:

*/
INSERT INTO #TableParent VALUES ( 1,'First Assignment', '05/01/2008')
INSERT INTO #TableParent VALUES ( 2,'Second Assignment','05/03/2008')
INSERT INTO #TableParent VALUES ( 3,'Third Assignment','05/07/2008')

INSERT INTO #TableChild VALUES (1,55342)
INSERT INTO #TableChild VALUES (2,33456)
INSERT INTO #TableChild VALUES (2,52343)
INSERT INTO #TableChild VALUES (3,35225)
INSERT INTO #TableChild VALUES (3,45121)
INSERT INTO #TableChild VALUES (3,11553)

select p.assignNum, p.description, p.startDate,
stuff((select ', ' + cast(employeeNum as varchar(512))from #TableChild c where c.ASSIGNNUM = p.ASSIGNNUM for xml path('')),1,2,'') as employeeList
from #TableParent p
;

This method is same as the one I posted. See that link

Madhivanan

Failing to plan is Planning to fail

f_tonmoy
Starting Member

1 Post

Posted - 2011-01-26 : 10:06:29

you can get help from my website
visit here http://www.bdlab.byethost32.com/index.php/sql-server/97-sql-query-for-multiple-row-to-single-column

http://www.bdlab.byethost32.com

cknight44
Starting Member

3 Posts

Posted - 2012-01-25 : 18:34:56

visakh16, this is awesome! Thanks. This a great technique.

quote:

Originally posted by visakh16

SELECT p.ASSIGNNUM,p.DESC,p.STARTDATE,LEFT(el.EmpList,LEN(el.EmpList)-1)
FROM TableParent p
CROSS APPLY (SELECT EMPLOYEENUM + ',' AS [text()]
             FROM TableChild
             WHERE ASSIGNNUM =p.ASSIGNNUM 
             FOR XML PATH(''))el(EmpList)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 09:04:30

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cris_0007
Starting Member

3 Posts

Posted - 2012-05-07 : 12:44:14

quote:
Originally posted by cknight44

visakh16, this is awesome! Thanks. This a great technique.

quote:
Originally posted by visakh16

SELECT p.ASSIGNNUM,p.DESC,p.STARTDATE,LEFT(el.EmpList,LEN(el.EmpList)-1)
FROM #TableParent p
CROSS APPLY (SELECT EMPLOYEENUM + ',' AS [text()]
FROM #TableChild
WHERE ASSIGNNUM =p.ASSIGNNUM
FOR XML PATH('')) el(EmpList)

I am getting the error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ',' to data type int.

when I run this

CREATE TABLE #TableParent (ASSIGNNUM int primary key, DESCC varchar(512), STARTDATE datetime)
CREATE TABLE #TableChild (ASSIGNNUM int, EMPLOYEENUM int)

/*
There could be multiple employees for each assignment. Sample data:

*/
INSERT INTO #TableParent VALUES ( 1,'First Assignment', '05/01/2008')
INSERT INTO #TableParent VALUES ( 2,'Second Assignment','05/03/2008')
INSERT INTO #TableParent VALUES ( 3,'Third Assignment','05/07/2008')

INSERT INTO #TableChild VALUES (1,55342)
INSERT INTO #TableChild VALUES (2,33456)
INSERT INTO #TableChild VALUES (2,52343)
INSERT INTO #TableChild VALUES (3,35225)
INSERT INTO #TableChild VALUES (3,45121)
INSERT INTO #TableChild VALUES (3,11553)

--select * from #TableParent
--select * from #TableChild

SELECT p.ASSIGNNUM,p.DESCC,p.STARTDATE,LEFT(el.EmpList,LEN(el.EmpList)-1)
FROM #TableParent p
CROSS APPLY (SELECT EMPLOYEENUM + ',' AS [text()]
FROM #TableChild
WHERE ASSIGNNUM =p.ASSIGNNUM
FOR XML PATH('')) as el(EmpList)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 17:28:32

[code]
SELECT p.ASSIGNNUM,p.DESCC,p.STARTDATE,LEFT(el.EmpList,LEN(el.EmpList)-1)
FROM #TableParent p
CROSS APPLY (SELECT CAST(EMPLOYEENUM AS varchar(10)) + ',' AS [text()]
FROM #TableChild
WHERE ASSIGNNUM =p.ASSIGNNUM
FOR XML PATH('')) as el(EmpList)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources