原始数据
id(IX) qty
001 5
001 5
001 2
001 5
001 1
003 6
003 6
003 1
003 1
003 6
003 6
003 6
005 4
005 4
005 4
005 3
005 3
005 3
变成下列的统计数据
id memo
001 5X3+2X1+1X1
003 6X5+1X2
005 4X3+3X3
行业数据
------解决方案--------------------
if exists(select * from sysobjects where name= 'st')
drop table st
go
create table st
(
id nvarchar(10),
qty int
)
go
insert into st
select '001',5 union all
select '001',5 union all
select '001',5 union all
select '001',2 union all
select '001',2 union all
select '002',4 union all
select '002',4 union all
select '002',6 union all
select '002',6
go
select id,
stuff((select '+'+convert(nvarchar(10),qty)+'*'+convert(nvarchar(10),count(qty)) from st st1
where st1.id = st2.id group by id,qty for xml path('')),1,1,'')d
from st st2
group by id
------解决方案--------------------
create table yon
(id varchar(5), qty int)
insert into yon
select '001', 5 union all
select '001', 5 union all
select '001', 2 union all
select '001', 5 union all
select '001', 1 union all
select '003', 6 union all
select '003', 6 union all
select '003', 1 union all
select '003', 1 union all
select '003', 6 union all
select '003', 6 union all
select '003', 6 union all
select '005', 4 union all
select '005', 4 union all
select '005', 4 union all
select '005', 3 union all
select '005', 3 union all
select '005', 3
with t as
(select id,rtrim(qty)+'X'+rtrim(count(1)) 's',
row_number() over(partition by id order by getdate()) 'rn'
from yon group by id,qty
)
select a.id,
stuff((select '+'+b.s
from t b
where b.id=a.id
order by b.rn desc
for xml path('')),1,1,'') 'memo'
from t a
group by a.id
/*
id memo