January 20, 2016 at 6:49 am
Hi,
CREATE TABLE #temp
(
ParentID INT,
TaskID INT,
TaskName NVARCHAR(MAX)
)
INSERT INTO #temp (ParentID,TaskID,TaskName)
SELECT 1,1,'Timesheet' UNION ALL
SELECT 1,2,'Jan15' UNION ALL
SELECT 1,3,'Feb15' UNION ALL
SELECT 2,2,'Parent Task' UNION ALL
SELECT 2,1,'Child Task'
SELECT * FROM #temp
SELECT t1.ParentID,t1.TaskID,
STUFF(
(SELECT DISTINCT ', ' + ( t2.[TaskName])
FROM #temp t2
where t1.ParentID = t2.ParentID
FOR XML PATH (''))
, 1, 1, '') AS List
FROM #temp t1
--Desired result (need a | character before the parent task followed by child with commas
SELECT 1 AS ParentID,1 AS TaskID,'Timesheet | Feb15,Jan15' UNION ALL
SELECT 1 AS ParentID,2 AS TaskID,'Timesheet | Feb15,Jan15' UNION ALL
SELECT 1 AS ParentID,3 AS TaskID,'Timesheet | Feb15,Jan15' UNION ALL
SELECT 2 AS ParentID,2 AS TaskID,' Parent Task | Child Task' UNION ALL
SELECT 2 AS ParentID,1 AS TaskID,' Parent Task | Child Task'
DROP TABLE #temp
Thanks,
PSB
January 20, 2016 at 7:07 am
You really need to understand how the code works before implementing it. STUFF won't concatenate anything, it'll just exchange strings. There are plenty of articles that explain how this concatenation works.
That said, it's easier if you just concatenate the children with FOR XML PATH and use normal concatenation operator (+) for the parent.
SELECT t1.ParentID,
t1.TaskID,
MAX( CASE WHEN ParentID = TaskID THEN TaskName END) OVER(PARTITION BY ParentID) + ' | '
+ STUFF((SELECT ',' + ( t2.[TaskName])
FROM #temp t2
where t1.ParentID = t2.ParentID
AND t2.TaskID <> t2.ParentID
--ORDER BY ?????
FOR XML PATH (''))
, 1, 1, '') AS List
FROM #temp t1
January 20, 2016 at 8:39 am
Thanks. That worked.
January 20, 2016 at 8:45 am
PSB (1/20/2016)
Thanks. That worked.
Great! Do you understand it?
January 21, 2016 at 7:04 am
Is there any way we can get rid of MAX from MAX( CASE WHEN TaskParentUID = TaskUID THEN TaskName END) OVER(PARTITION BY TaskParentUID) + ' | '
I am getting Warning: Null value is eliminated by an aggregate or other SET operation. and getting Null value in the list
January 21, 2016 at 7:14 am
PSB (1/21/2016)
Is there any way we can get rid of MAX from MAX( CASE WHEN TaskParentUID = TaskUID THEN TaskName END) OVER(PARTITION BY TaskParentUID) + ' | 'I am getting Warning: Null value is eliminated by an aggregate or other SET operation. and getting Null value in the list
I don't get why that would be an issue. The warning is expected as we're generating nulls to ensure the max value. A different option would be using FIRST_VALUE().
SELECT t1.ParentID,
t1.TaskID,
FIRST_VALUE( TaskName) OVER(PARTITION BY ParentID ORDER BY NULLIF(TaskID, ParentID)) + ' | '
+ STUFF((SELECT ',' + ( t2.[TaskName])
FROM #temp t2
where t1.ParentID = t2.ParentID
AND t2.TaskID <> t2.ParentID
--ORDER BY ?????
FOR XML PATH (''))
, 1, 1, '') AS List
FROM #temp t1
January 21, 2016 at 7:27 am
Actually there is an issue with the data . We do not always have ParentID (71D5ECC4-93B4-E511-AD48-24FD5236B149) = TaskID , so this will lead to NULL values . If PArentID <> TaskID then we will display Child 1, Child 2 else Parent 1 | Child 1, Child 2
CREATE TABLE #temp
(
ParentID UNIQUEIDENTIFIER,
TaskID UNIQUEIDENTIFIER,
TaskName NVARCHAR(MAX)
)
INSERT INTO #temp (ParentID,TaskID,TaskName)
SELECT '71D5ECC4-93B4-E511-AD48-24FD5236B149','73D5ECC4-93B4-E511-AD48-24FD5236B149','Timesheet' UNION ALL
SELECT '71D5ECC4-93B4-E511-AD48-24FD5236B149','75D5ECC4-93B4-E511-AD48-24FD5236B149','Jan15' UNION ALL
SELECT '71D5ECC4-93B4-E511-AD48-24FD5236B149','7BD5ECC4-93B4-E511-AD48-24FD5236B149','Vision 5' UNION ALL
SELECT '2CDA254C-C0B3-E511-80F3-005056AD5182','2CDA254C-C0B3-E511-80F3-005056AD5182','Activity 1' UNION ALL
SELECT '2CDA254C-C0B3-E511-80F3-005056AD5182','C92E8F80-C1B3-E511-8AB3-D4BED934DBD1','Activity 2' UNION ALL
SELECT '2CDA254C-C0B3-E511-80F3-005056AD5182','CB2E8F80-C1B3-E511-8AB3-D4BED934DBD1','Activity 3' UNION ALL
SELECT '2CDA254C-C0B3-E511-80F3-005056AD5182','CF2E8F80-C1B3-E511-8AB3-D4BED934DBD1','Activity 4'
SELECT * FROM #temp
SELECT t1.ParentID,
t1.TaskID,
MAX( CASE WHEN ParentID = TaskID THEN TaskName END) OVER(PARTITION BY ParentID) + ' | '
+ STUFF((SELECT ',' + ( t2.[TaskName])
FROM #temp t2
where t1.ParentID = t2.ParentID
AND t2.TaskID <> t2.ParentID
--ORDER BY ?????
FOR XML PATH (''))
, 1, 1, '') AS List
FROM #temp t1
DROP TABLE #temp
January 21, 2016 at 7:40 am
I am doing MAX( CASE WHEN ParentID = TaskID THEN TaskName WHEN ParentID <> TaskID THEN ''
END) OVER(PARTITION BY ParentID) + ' | '
to eliminate NULL values . Getting --> | Timesheet,Jan15,Vision 5 but would be nice to get Timesheet,Jan15,Vision 5
January 21, 2016 at 7:50 am
Keeping the nulls resulting from the MAX will help you to get the correct format using ISNULL()
SELECT t1.ParentID,
t1.TaskID,
ISNULL( MAX( CASE WHEN ParentID = TaskID THEN TaskName END) OVER(PARTITION BY ParentID) + ' | ' , '')
+ STUFF((SELECT ',' + ( t2.[TaskName])
FROM #temp t2
WHERE t1.ParentID = t2.ParentID
AND t2.TaskID <> t2.ParentID
--ORDER BY ?????
FOR XML PATH (''))
, 1, 1, '') AS List
FROM #temp t1;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply