[solved] Concatenate Detail data per Master record

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

Transact-SQL (2008)

[solved] Concatenate Detail data per Master record

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-13 : 05:52:24

I have a Master table with two detail tables.
I'm trying to get a resultset back containing a row for each Master record, with the data of the Detail tables concatenated in one field.

The following is the best result I got so far

If OBJECT_ID(N'testDetail1',N'U') IS NOT NULL
Drop Table testDetail1;
GO
If OBJECT_ID(N'testDetail2',N'U') IS NOT NULL
Drop Table testDetail2;
GO
If OBJECT_ID(N'testMaster',N'U') IS NOT NULL
Drop Table testMaster;
GO

Create Table testMaster(ID int identity(1,1) not null primary key, Code varchar(50) not null, MasterDueDate datetime null);
GO
Create Table testDetail1(ID int identity(1,1) not null primary key, FK_Master_ID int not null references testMaster, Detail1DueDate datetime null);
GO
Create Table testDetail2(ID int identity(1,1) not null primary key, FK_Master_ID int not null references testMaster, Detail2DueDate datetime null);
GO

insert into testMaster(Code, MasterDueDate) values('Code 1', '20110901');
GO
insert into testMaster(Code, MasterDueDate) values('Code 2', NULL);
GO

insert into testDetail1(FK_Master_ID, Detail1DueDate) values (1, '20110910');
GO
insert into testDetail1(FK_Master_ID, Detail1DueDate) values (1, NULL);
GO
insert into testDetail1(FK_Master_ID, Detail1DueDate) values (1, '20110912');
GO

insert into testDetail2(FK_Master_ID, Detail2DueDate) values (1, '20110801');
GO
insert into testDetail2(FK_Master_ID, Detail2DueDate) values (2, '20110920');
GO
insert into testDetail2(FK_Master_ID, Detail2DueDate) values (2, '20110921');
GO
insert into testDetail2(FK_Master_ID, Detail2DueDate) values (2, NULL);
GO

-------------------------------------------

select m.Code,
       convert(varchar(11),m.MasterDueDate,113) [MasterDueDate],
       (select STUFF    
         (    
           (select CASE WHEN d1.Detail1DueDate IS NOT NULL THEN ', ' + convert(varchar(11),d1.Detail1DueDate,113) ELSE '' END +     
                   CASE WHEN d2.Detail2DueDate IS NOT NULL THEN ', ' + convert(varchar(11),d2.detail2DueDate,113) ELSE '' END
            from testMaster m
            left join testDetail1 d1
              on d1.FK_Master_ID = m.ID
            left join testDetail2 d2
              on d2.FK_Master_ID = m.ID            
            for xml path('')    
            ),1,2,''    
         )    
       ) [Details DueDates]
from testMaster m;
GO

-------------------------------------------

drop table testDetail1;
GO
drop table testDetail2;
GO
drop table testMaster;
GO

Result

Code 1	01 Sep 2011	10 Sep 2011, 01 Aug 2011, 01 Aug 2011, 12 Sep 2011, 01 Aug 2011, 20 Sep 2011, 21 Sep 2011
Code 2	NULL		10 Sep 2011, 01 Aug 2011, 01 Aug 2011, 12 Sep 2011, 01 Aug 2011, 20 Sep 2011, 21 Sep 2011

Expected Result

Code 1	01 Sep 2011	10 Sep 2011, 12 Sep 2011, 01 Aug 2011
Code 2	NULL		20 Sep 2011, 21 Sep 2011

Can someone point me to what I need to do to get the correct result?
Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 06:16:21

see

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

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-13 : 07:37:04

try


            from testMaster m
            left join testDetail1 d1
              on d1.FK_Master_ID = m.ID
            left join testDetail2 d2
              on d2.FK_Master_ID = m.ID            
	    group by d1.Detail1DueDate, d2.detail2DueDate
            for xml path('')

KH
[spoiler]Time is always against us[/spoiler]

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-13 : 07:45:00

Thanks for the link. Something similar is indeed what I'm trying to accomplish. And as you can see, I am already using the STUFF function.
But the difference with my example and the one in the link provided, is I have one to many relations. Resulting in all detail data for each master record.
I can't seem to find a query which produces the result I want. Could someone tell me what I'm missing?
Thanks.

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-13 : 07:47:58

Thanks khtan, but the only difference is the order of the concatenated elements. I still get all detail data for each master

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 07:59:38

[code]
select m.Code,
convert(varchar(11),m.MasterDueDate,113) [MasterDueDate],
(select STUFF
(
(select CASE WHEN d1.Detail1DueDate IS NOT NULL THEN ', ' + convert(varchar(11),d1.Detail1DueDate,113) ELSE '' END from testDetail1 d1
where d1.FK_Master_ID = m.ID
for xml path('')) +
(select CASE WHEN d2.Detail2DueDate IS NOT NULL THEN ', ' + convert(varchar(11),d2.detail2DueDate,113) ELSE '' END

from testDetail2 d2
where d2.FK_Master_ID = m.ID
for xml path('')
),1,2,''
)
) [Details DueDates]
from testMaster m;
[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-13 : 08:04:57

[code]
select m.Code,
convert(varchar(11),m.MasterDueDate,113) [MasterDueDate],
stuff(
(
select ', ' + convert(varchar(11),d.DetailDueDate,113)
from (
select sort = 1, DetailDueDate = Detail1DueDate from testDetail1 d1 where d1.FK_Master_ID = m.ID
union all
select sort = 2, DetailDueDate = Detail2DueDate from testDetail2 d2 where d2.FK_Master_ID = m.ID
) d
order by sort, DetailDueDate
for xml path('')
) , 1, 2, '') [Details DueDates]
from testMaster m
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-13 : 08:16:32

Thanks visakh16, almost what I need. I see what you're trying to do, but somehow it doesn't get the Detail information for the second Master record (the Detail information for the first Master record is correct though).

Thanks khtan, your query did indeed return the result I wanted. I still need to figure out what you're doing here (it looks over complicated) and see if I can use it for all my scenarios.

Thanks to both for all the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:26:51

[code]
select m.Code,
convert(varchar(11),m.MasterDueDate,113) [MasterDueDate],
(select STUFF
(
coalesce((select CASE WHEN d1.Detail1DueDate IS NOT NULL THEN ', ' + convert(varchar(11),d1.Detail1DueDate,113) ELSE '' END from testDetail1 d1
where d1.FK_Master_ID = m.ID
for xml path('')),'') +
coalesce((select CASE WHEN d2.Detail2DueDate IS NOT NULL THEN ', ' + convert(varchar(11),d2.detail2DueDate,113) ELSE '' END

from testDetail2 d2
where d2.FK_Master_ID = m.ID
for xml path('')
),''),1,2,''
)
) [Details DueDates]
from testMaster m;

[/code]

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

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-13 : 08:41:46

Now I have two different ways to get my result!
Again, many thanks to both of you!

I will be using visakh16's way because it is easier to understand and adapt to the rest of my query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:43:25

no problem
you're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources