mercredi 21 septembre 2016

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

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

Aucun commentaire:

Enregistrer un commentaire