How to rollup columns

Question

We are running SQL Server 2005. I am having problems rolling up columns.

create table group_roll 
(
    id_name int,
    full_name varchar(50),
    qty int
)
go

insert into group_roll (id_name, full_name, qty) 
values (1, 'john smith', 10)

insert into group_roll (id_name, full_name, qty) 
values (1, 'john smith', 40)

insert into group_roll (id_name, full_name, qty) 
values (1, 'jane smith', 50)

insert into group_roll (id_name, full_name, qty) 
values (1, 'dean smith', 10)

insert into group_roll (id_name, full_name, qty) 
values (2, 'frank white', 5)

insert into group_roll (id_name, full_name, qty) 
values (2, 'Ann white', 12)

insert into group_roll (id_name, full_name, qty) 
values (1, 'john smith', 8)

insert into group_roll (id_name, full_name, qty) 
values (2, 'frank white', 10)

insert into group_roll (id_name, full_name, qty) 
values (3, 'perry mason', 10)
go

select * 
from group_roll
order by id_name

Output:

   id_name       full_name          qty
   --------------------------------------
    1            john smith          10
    1            john smith          40
    1            jane smith          50
    1            dean smith          10
    1            john smith           8
    2            frank white         10
    2            frank white          5
    2            Ann white           12
    3            perry mason         10

I want the result to be rolled up into something like this

    id_name  name                                   qty
    ----------------------------------------------------
     1       john smith, jane smith, dean smith     118
     2       frank white, ann white                  27
     3       perry mason                             10

How do you code to rollup names and qty as shown?

Thank you,

Seyed

score 1 · Accepted Answer · answered Sep 21 '16 at 14:35

Try this: It will give you what you expected but the output you have provided, if i am not wrong the third row holding wrong value:

SELECT id_name,
STUFF((SELECT DISTINCT ', ' + full_name FROM group_roll A
    WHERE A.id_name=b.id_name FOR XML PATH('')),1,1,'') As name,
SUM(qty) qty               
FROM group_roll b
GROUP BY id_name

How to rollup columns

1 Answers1