在sql server 里,可以这么合并数据:
with t
as
(
select 1 as id,'产品1' p,'类型1' as v union all
select 2 as id,'产品2','类型1' as v union all
select 3 as id,'产品3','类型1' as v union all
select 4 as id,'产品1','类型2' as v union all
select 5 as id,'产品4','类型2' as v union all
select 6 as id,'产品5','类型3' as v union all
select 7 as id,'产品6','类型3' as v union all
select 8 as id,'产品7','类型3' as v union all
select 9 as id,'产品8','类型3' as v union all
select 10 as id,'产品9','类型4' as v
)
select v,
stuff(cast((select ','+tt.p from t tt where t.v = tt.v for xml path('')) as varchar(100)),1,1,'') as p
from t
group by v
/*
v p
类型1 产品1,产品2,产品3
类型2 产品1,产品4
类型3 产品5,产品6,产品7,产品8
类型4 产品9
*/
那如果在合并的同时,要把多个数据,显示成多行要如何处理呢?
有两个办法:
(1)就是在拼接数据时,加上 回车符号,也就是char(10),这样:
with t
as
(
select 1 as id,'产品1' p,'类型1' as v union all
select 2 as id,'产品2','类型1' as v union all
select 3 as id,'产品3','类型1' as v union all
select 4 as id,'产品1','类型2' as v union all
select 5 as id,'产品4','类型2' as v union all
select 6 as id,'产品5','类型3' as v union all
select 7 as id,'产品6','类型3' as v union all
select 8 as id,'产品7','类型3' as v union all
select 9 as id,'产品8','类型3' as v union all
select 10 as id,'产品9','类型4' as v
)
select v,
stuff(cast((select char(10)+tt.p from t tt where t.v = tt.v for xml path('')) as varchar(100)),1,1,'') as p
from t
group by v
查询结果的数据,复制出来,就是换行的,格式不太好看:
v p
类型1 产品1
产品2
产品3
类型2 产品1
产品4
类型3 产品5
产品6
产品7
产品8
类型4 产品9
(2)在网页中显示的数据要现实成多行,这种情况更常见一点。
其实就是加上<br>标签,但是这个标签在for xml path('')时,会被转化为 <br> ,所以,数据合并之后,还需要用replace函数进行替换:
with t
as
(
select 1 as id,'产品1' p,'类型1' as v union all
select 2 as id,'产品2','类型1' as v union all
select 3 as id,'产品3','类型1' as v union all
select 4 as id,'产品1','类型2' as v union all
select 5 as id,'产品4','类型2' as v union all
select 6 as id,'产品5','类型3' as v union all
select 7 as id,'产品6','类型3' as v union all
select 8 as id,'产品7','类型3' as v union all
select 9 as id,'产品8','类型3' as v union all
select 10 as id,'产品9','类型4' as v
)
select v,
replace(
stuff(cast((select '<br>'+tt.p from t tt where t.v = tt.v for xml path('')) as varchar(100)),1,10,''),
'<br>',
'<br>'
) as p
from t
group by v
结果如下:
类型1 产品1<br>产品2<br>产品3
类型2 产品1<br>产品4
类型3 产品5<br>产品6<br>产品7<br>产品8
类型4 产品9