sql server 2008 存储过程 将多条数据合并为一条
数据1
MARS_TPN2MARS_CPN2ERG1SJ471V MARS_TTA(1)
MARS_TPN7MARS_CPN7135942-61500910 MARS_TTA(2)
MARS_TPN7MARS_CPN7135942-61500910 MARS_TTC(1)
数据2
MARS_TPN2MARS_CPN2ERG1SJ471V MARS_TTA(1)
MARS_TPN7MARS_CPN7135942-61500910 MARS_TTA(2),MARS_TTC(1)
如何用存储过程，将数据1的数据转化为数据2的数据
求解
在线等
急。。。。。。。。。。。。。。。。。。。。。
将多条数据合并为一条
[解决办法]
存储过程不用写了吧？
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-19 17:39:10
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([a] varchar(9),[b] varchar(9),[c] varchar(15),[d] varchar(11))
insert [huang]
select 'MARS_TPN2','MARS_CPN2','ERG1SJ471V','MARS_TTA(1)' union all
select 'MARS_TPN7','MARS_CPN7','135942-61500910','MARS_TTA(2)' union all
select 'MARS_TPN7','MARS_CPN7','135942-61500910','MARS_TTC(1)'
--------------开始查询--------------------------
select a.a,a.b,a.c,
stuff((select ','+d from [huang] b
where b.a=a.a and b.b=a.b and b.c=a.c
for xml path('')),1,1,'') 'd'
from [huang] a
group by a.a,a.b,a.c
----------------结果----------------------------
/*
a b c d
--------- --------- --------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MARS_TPN2 MARS_CPN2 ERG1SJ471V MARS_TTA(1)
MARS_TPN7 MARS_CPN7 135942-61500910 MARS_TTA(2),MARS_TTC(1)
*/
if object_id('tb') is not null drop table tb
go
create table tb([a] varchar(9),[b] varchar(9),[c] varchar(15),[d] varchar(11))
insert tb
select 'MARS_TPN2','MARS_CPN2','ERG1SJ471V','MARS_TTA(1)' union all
select 'MARS_TPN7','MARS_CPN7','135942-61500910','MARS_TTA(2)' union all
select 'MARS_TPN7','MARS_CPN7','135942-61500910','MARS_TTC(1)'
go
--drop proc dbo.proc_test
--go
create proc dbo.proc_test
as
select a.a,a.b,a.c,
stuff(
(select ','+d
from tb b
where b.a=a.a and b.b=a.b and b.c=a.c
for xml path('')
),
1,1,''
) as d
from tb a
group by a.a,a.b,a.c
go
exec dbo.proc_test
/*
a b c d
MARS_TPN2MARS_CPN2ERG1SJ471VMARS_TTA(1)
MARS_TPN7MARS_CPN7135942-61500910MARS_TTA(2),MARS_TTC(1)
*/
