SQL Server中实现数据换行

在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('')时，会被转化为 &lt;br&gt; ，所以，数据合并之后，还需要用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