Friday, August 28, 2009

PIVOT table result

Table
create table source(id int identity(1,1)not null,postcode varchar(10),cust varchar(10))

insert into source(postcode,cust)values('AB10','AMI004');
insert into source(postcode,cust)values('AB10','CLI001');
insert into source(postcode,cust)values('AB10','HCL001');
insert into source(postcode,cust)values('AB10','MIL003');
insert into source(postcode,cust)values('AB10','OSB001');
insert into source(postcode,cust)values('AB11','AMI004');
insert into source(postcode,cust)values('AB11','CLI001');
insert into source(postcode,cust)values('AB11','HCL001');
insert into source(postcode,cust)values('AB11','MIL003');
insert into source(postcode,cust)values('AB11','OSB001');
insert into source(postcode,cust)values('AB12','AMI004');
insert into source(postcode,cust)values('AB12','CLI001');
insert into source(postcode,cust)values('AB12','ABC001');
insert into source(postcode,cust)values('AB12','THK010');
insert into source(postcode,cust)values('AB12','NHF001');
insert into source(postcode,cust)values('AB12','HGF002');


select postcode,
replace((
select cust as "data()"
from source b
where b.postcode = a.postcode
for xml path('')
), ' ', ', ') as cust
from source a
group by postcode

No comments:

Post a Comment