Concatinate related records

Hello,

As title says I need to contatinate related records.

Here is the sample code including my comments
DECLARE @TempTable TABLE
(JobName VARCHAR(MAX),
NextTime CHAR(7),
NextDay CHAR(3)
)
;
INSERT INTO @TempTable
(JobName,
NextTime,
NextDay
)
(
SELECT 'test','1:00PM','Wed' UNION ALL               
SELECT 'test','5:00PM','Tue' UNION ALL                     
SELECT 'shell','9:00PM','Tue' UNION ALL                     
SELECT 'shell','9:00PM','Tue' UNION ALL                   
SELECT 'board','9:00PM','Tue' UNION ALL                   
SELECT 'custom','10:00PM','Tue' UNION ALL                 
SELECT 'DATA','8:00PM','Tue'
)

SELECT * FROM @TempTable
ORDER BY NextTime, NextDay 


/* 
	****************************************
	** THIS IS WHAT I SEE
	****************************************
	JobName				NextTime	 NextDay
	------------------------------------------
	test				1:00PM			Wed
	custom				10:00PM			Tue
	test				5:00PM			Tue
	DATA				8:00PM			Tue
	shell				9:00PM			Tue
	shell				9:00PM			Tue
	board				9:00PM			Tue
	******************************************
	HOWEVER I WANT THE FOLLOWING (concatination)
	see on the bottom
	------------------------------------------
	JobName				NextTime	 NextDay
	------------------------------------------
	test				1:00PM			Wed
	custom				10:00PM			Tue
	test				5:00PM			Tue
	DATA				8:00PM			Tue
	shell,shell,board   9:00PM			Tue
*/

Open in new window

LVL 1
smanoAsked:
Who is Participating?
 
SharathData EngineerCommented:
try this.
select distinct rtrim(substring(isnull((select ','+JobName from @TempTable t2 where t1.NextTime = t2.NextTime and t1.NextDay = t2.NextDay 
                                              for xml path('')),' '),2,2000)) JobName,NextTime,NextDay
from @TempTable t1
ORDER BY NextTime, NextDay 

Open in new window

0
 
smanoAuthor Commented:
Cool. And can you without the xml path or is it necessary?
0
 
Ephraim WangoyaCommented:
Try this
DECLARE @TempTable TABLE
(JobName VARCHAR(MAX),
NextTime CHAR(7),
NextDay CHAR(3)
)
;
INSERT INTO @TempTable
(JobName,
NextTime,
NextDay
)
(
SELECT 'test','1:00PM','Wed' UNION ALL               
SELECT 'test','5:00PM','Tue' UNION ALL                     
SELECT 'shell','9:00PM','Tue' UNION ALL                     
SELECT 'shell','9:00PM','Tue' UNION ALL                   
SELECT 'board','9:00PM','Tue' UNION ALL                   
SELECT 'custom','10:00PM','Tue' UNION ALL                 
SELECT 'DATA','8:00PM','Tue'
)

select stuff((select ',' + JobName	
         from @TempTable B
         where B.NextDay = A.NextDay
         and B.NextTime = A.NextTime
         for XML Path('')), 1,1,'') JobName,
       NextTime, NextDay
from @TempTable A  
group by NextDay, NextTime

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
>> And can you without the xml path or is it necessary?
xml is cool stuff in 2005 you can use it if possible. If you don't want to use xml, you can get the result with one more temp table.
check the below code.
DECLARE @TempTable TABLE
(JobName VARCHAR(MAX),
NextTime CHAR(7),
NextDay CHAR(3)
)
;
INSERT INTO @TempTable
(JobName,
NextTime,
NextDay
)
(
SELECT 'test','1:00PM','Wed' UNION ALL               
SELECT 'test','5:00PM','Tue' UNION ALL                     
SELECT 'shell','9:00PM','Tue' UNION ALL                     
SELECT 'shell','9:00PM','Tue' UNION ALL                   
SELECT 'board','9:00PM','Tue' UNION ALL                   
SELECT 'custom','10:00PM','Tue' UNION ALL                 
SELECT 'DATA','8:00PM','Tue'
)

declare @TempTable1 table (JobName VARCHAR(100),NextTime CHAR(7),NextDay CHAR(3),JobList varchar(500))
declare @NextTime char(7),@NextDay char(3),@lists varchar(500)

insert @TempTable1(JobName,NextTime,NextDay)
select JobName,NextTime,NextDay from @TempTable

UPDATE @TempTable1
SET @lists = CASE WHEN @NextTime = NextTime and @NextDay = NextDay THEN @lists + ', ' + JobName ELSE ', ' + JObName END,
	JobList = @lists,
	@NextDay = NextDay,
	@NextTime = NextTime

select MAX(substring(JobList,2,LEN(JobList))) JobName,NextTime,NextDay
  from @TempTable1
 group by NextTime,NextDay
 ORDER BY NextTime, NextDay 
/*
JobName	NextTime	NextDay
 test	1:00PM 	Wed
 custom	10:00PM	Tue
 test	5:00PM 	Tue
 DATA	8:00PM 	Tue
 shell, shell, board	9:00PM 	Tue
*/

Open in new window

0
 
smanoAuthor Commented:
Concatinate
0
 
smanoAuthor Commented:
VERY HELPFUL again. Thanks!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.