sql2008行转列方法

水晶烟灰缸 2014-07-08 09:38:16

name type
--------------------
test1 t1
test1 t2
test1 t3
.
. 中间有N个
.
test1 tn

转换为
name type
--------------------
test1 t1,t2,t3,...,tn

...全文

177 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

jiajiaren 2014-07-08

打赏
举报

 
IF OBJECT_ID(N'Test') IS NOT  NULL
DROP TABLE Test
GO
CREATE  TABLE Test(name  VARCHAR(10),TYPE VARCHAR(10))
INSERT INTO Test SELECT 'test1','t1'
UNION ALL SELECT  'test1','t2'
UNION ALL SELECT  'test1','t3'
 


--------------------------------------------------------------------------- 查询-----------------------------------------------
SELECT name, STUFF((SELECT  ','+TYPE FROM Test b WHERE a.name=b.NAME FOR XML PATH('')),1,1,'') AS TYPE FROM  Test a GROUP BY name

---------------------------------------------------------------------------结果-----------------------------------------------
/*name       TYPE
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test1      t1,t2,t3

(1 行受影响)
*/