Coalesce and Pivot in TSQL

Question

I am having trouble figuring out how to coalesce or pivot on a SQL recordset that looks like this:

ID   VALUE  GROUP
3    John  18
4    Smith  18
5    Microsoft 18
3    Randy  21
4    Davis  21
5    IBM  21
etc

and I want formatted like this

NEWVALUE       GROUP
Smith, John (Microsft)   18
Davis, Randy (IBM)    21

thanks for any suggestions and help!

Answer 1

What you need is not pivoted query but a simple select with group by and an aggregate string concatenation function. But i don't remember the exact function in tsql.

Update: there is no aggregate concatenation function in tsql but since sql2005 you can write your own extension to implement such function. There is plenty of examples on google search for: tsql 2005 concatenation aggregate example.

Answer 2

This is what i done, i hope it fits for you

DECLARE @t table (id int, value VARCHAR(20), grupo int)
INSERT @T VALUES (3, 'John', 18)
INSERT @T VALUES (4, 'Smith', 18)
INSERT @T VALUES (5, 'Microsoft', 18)
INSERT @T VALUES (3, 'Randy', 21)
INSERT @T VALUES (4, 'Davis', 21)
INSERT @T VALUES (5, 'IBM', 21)


SELECT grupo, (SELECT value FROM @t t2 WHERE t2.grupo = t.grupo AND id = 4) + ', ' + 
(SELECT value FROM @t t2 WHERE t2.grupo = t.grupo AND id = 3) + ' (' +
(SELECT value FROM @t t2 WHERE t2.grupo = t.grupo AND id = 5) + ')'
FROM @t t 
GROUP BY grupo

Answer 3

SELECT  LEFT(gvalue, LEN(gvalue) - 1) AS newvalue, _group
FROM    (
        SELECT  DISTINCT _group
        FROM    mytable
        ) qo
CROSS APPLY
        (
        SELECT  value + ', '
        FROM    mytable qi
        WHERE   qi._group = qo._group
        FOR XML PATH ('')
        ) gr(qvalue)

If you always have a set of three hardcoded ID's for each _group, you can just use:

SELECT m3._group, m3.value + ', ' + m4.value + '(' + m5.value + ')' AS newvalue
FROM   mytable m3
LEFT JOIN
       mytable m4
ON     m4._group = m3.group
LEFT JOIN
       mytable m5
ON     m5._group = m3.group
WHERE  m3.id = 3
       AND m4.id = 4
       AND m5.id = 5

Answer 4

This is a little hokey, but I think it should work reasonably well for a small data set. If you've got a lot of data you need to create a cursor and a loop.

select max(case when ID = 4 then VALUE else null end) + ', ' + 
    max(case when ID = 4 then VALUE else null end) + '( ' +
    max(case when ID = 5 then VALUE else null end) + ') as NEWVALUE,
    [GROUP]
group by [GROUP]