| Author |
 Topic  |
|
|
lwhalen618
Starting Member
4 Posts |
 Posted - 05/07/2008 : 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
USA
933 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/08/2008 : 04:08:51
|
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) |
 |
|
|
lwhalen618
Starting Member
4 Posts |
Posted - 05/08/2008 : 22:59:36
|
| Thanks, everyone. I'm working through your suggestions and will report back my solution. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
|
lwhalen618
Starting Member
4 Posts |
|
|
Gawaine79
Starting Member
6 Posts |
Posted - 12/23/2009 : 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
India
22460 Posts |
Posted - 12/24/2009 : 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 Posts |
|
|
cknight44
Starting Member
USA
3 Posts |
Posted - 01/25/2012 : 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
India
47023 Posts |
Posted - 01/26/2012 : 09:04:30
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cris_0007
Starting Member
2 Posts |
Posted - 05/07/2012 : 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)
|
Edited by - cris_0007 on 05/07/2012 13:29:51 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/07/2012 : 17:28:32
|
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)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Concatenate multiple rows into one column?
