SeyedG SeyedG - 2 months ago 14
SQL Question

How to rollup columns

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

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 rick black 10


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

Thank you,

Seyed

Answer

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
Comments