CSV of child table IDs in Parent table Field

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 2000 Forums

SQL Server Development (2000)

CSV of child table IDs in Parent table Field

Author

Topic

Naveed88
Starting Member

19 Posts

Posted - 2009-02-09 : 23:30:34

Hello every one

i want to create csv of data in specific column in Child Table

and insert it into one cell in Master table

example

MasterTable
ID name
1 Naveed
2 Deevan
3 Jitendra

ChildTable
ID FK Items
1 1 90
2 1 91
3 2 92
4 2 93
5 3 94
6 3 95
7 1 96

Then i want to query for this result

ResultTable
ID name ItemPurchased
1 Naveed 90,91,96
2 Deevan 92,93
3 Jitendra 94,95

Thanks for Help

--
(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer
9867374437-Mumbai.4

Padmaja
Starting Member

6 Posts

Posted - 2009-02-09 : 23:55:03

DECLARE @MasterTable TABLE(ID INT,name VARCHAR(24))
INSERT INTO @MasterTable
SELECT 1, 'Naveed' UNION ALL
SELECT 2, 'Deevan' UNION ALL
SELECT 3, 'Jitendra'

DECLARE @ChildTable TABLE(ID INT, FK INT, Items INT)
INSERT INTO @ChildTable
SELECT 1, 1, 90 UNION ALL
SELECT 2, 1, 91 UNION ALL
SELECT 3, 2, 92 UNION ALL
SELECT 4, 2, 93 UNION ALL
SELECT 5, 3, 94 UNION ALL
SELECT 6, 3, 95 UNION ALL
SELECT 7, 1, 96

SELECT DISTINCT
mt.id,
mt.name,
STUFF((SELECT DISTINCT ','+CAST(items AS VARCHAR(128))
FROM @ChildTable AS ct
WHERE ct.fk = mt.id FOR XML PATH('')),1,1,'')
FROM
@MasterTable mt

padmaja

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 00:50:34

quote:
Originally posted by Padmaja

DECLARE @MasterTable TABLE(ID INT,name VARCHAR(24))
INSERT INTO @MasterTable
SELECT 1, 'Naveed' UNION ALL
SELECT 2, 'Deevan' UNION ALL
SELECT 3, 'Jitendra'

DECLARE @ChildTable TABLE(ID INT, FK INT, Items INT)
INSERT INTO @ChildTable
SELECT 1, 1, 90 UNION ALL
SELECT 2, 1, 91 UNION ALL
SELECT 3, 2, 92 UNION ALL
SELECT 4, 2, 93 UNION ALL
SELECT 5, 3, 94 UNION ALL
SELECT 6, 3, 95 UNION ALL
SELECT 7, 1, 96

SELECT DISTINCT
mt.id,
mt.name,
STUFF((SELECT DISTINCT ','+CAST(items AS VARCHAR(128))
FROM @ChildTable AS ct
WHERE ct.fk = mt.id FOR XML PATH('')),1,1,'')
FROM
@MasterTable mt

padmaja

This won't work in SQL 2000.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 00:53:53

[code]--Create Function Like This:

CREATE FUNCTION [dbo].[UDFNumberList]
(
@FK varchar(200)
)
RETURNS varchar(5000)
AS
BEGIN
DECLARE @Number varchar(8000)

SELECT @Number= COALESCE(@Number+',','') + convert(varchar(200),Items)
FROM dbo.CHILD
WHERE FK=@FK
RETURN @Number
END

Then use like this:

Select M.ID,M.Name,dbo.GetNumberList(FK) as NumberList
from MASTER M Inner join CHILD C
on M.ID = C.FK
Group by M.ID,M.NAME,C.FK[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 02:24:28

quote:

Originally posted by sodeep

--Create Function Like This:

CREATE FUNCTION [dbo].[UDFNumberList]
(
@FK varchar(200)
)
RETURNS varchar(5000) 
AS
BEGIN
DECLARE @Number varchar(8000)

SELECT @Number= COALESCE(@Number+',','') + convert(varchar(200),Items)
FROM dbo.CHILD
WHERE FK=@FK
ORDER BY Items
RETURN @Number
END

Then use like this:

Select M.ID,M.Name,dbo.GetNumberList(FK) as NumberList
from MASTER M Inner join CHILD C
on M.ID = C.FK
Group by M.ID,M.NAME,C.FK

to get results in expected order

Naveed88
Starting Member

19 Posts

Posted - 2009-02-10 : 04:46:21

wow
quit straight forward

thanks padmaja

and

Sodeep also

--
(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer
9867374437-Mumbai.4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources