November 11, 2016 at 11:06 am
Hi,
I have requirement to concatenate row values and was able to achieve using codeplex-groupconcat, but also need to have a same concat row to displayed as another column with just one row value.
CREATE TABLE Task
(
ID INT
,Tasks VARCHAR(MAX)
,Preference int
)
INSERT INTO Task
(ID,Tasks,Preference)
SELECT 600 ,'Task1' ,1
UNION SELECT 600 ,'Task2' ,2
UNION SELECT 600 ,'Task3' ,3
UNION SELECT 700 ,'Task1' ,2
UNION SELECT 700 ,'Task2' ,3
SELECT ID ,DBO.GROUP_CONCAT(Tasks) as tasks from #Demo
group by id
IDtasks
600Task1,Task2,Task3
700Task1,Task2
But I would also need to display another column like as Perferencetask, which is the first task for the ID.
IDtasks Perferencetask
600Task1,Task2,Task3 Task1
700Task1,Task2 Task1
Can anyone suggest a better way without a performance issues?
November 11, 2016 at 11:34 am
You can get the entire list of "preferred" tasks as follows:
SELECT ID, Task
FROM Tasks
WHERE Preference = 1
You could use that query as a CTE and then JOIN to it in your existing query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 18, 2016 at 8:03 am
select
tsk.ID,
(select top (1) t.Tasks from dbo.Task t where t.ID = tsk.ID and t.preference = min(tsk.Preference)) as [Preference Task],
stuff((
select ', ' + t.Tasks as [text()]
from dbo.Task t
where t.ID = tsk.ID
order by t.Preference
for xml path(''), type
).value('.','varchar(max)'), 1, 2, '') as Tasks
from dbo.Task tsk
group by
tsk.ID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply