smano
asked on
Concatinate related records
Hello,
As title says I need to contatinate related records.
Here is the sample code including my comments
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
*/ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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.
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
*/ASKER
Concatinate
ASKER
VERY HELPFUL again. Thanks!!!!
ASKER