Help with concatenating row values

  • 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?

  • 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)

  • 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



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply