[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

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

0
smano
Asked:
smano
  • 3
  • 2
2 Solutions
 
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now