如何將多個字段的值串成一個字符串形成另外一表的字段值?

[JB-Zhang]

有TabelA:

TeamCode Account
-----------------------
AA User1
AB User1
AC User1
AB User2
BC User2

我想以Account分組,將TeamCode字段值的內容串成字符串,形成如下Table2:

Account TeamCode
------------------------
User1 AA,AB,AC
User2 AB,BC

我能想到的是游標,請問還有其它更好的辦法嗎?

[發糞塗牆]

你在FAQ贴中找“行转列”，就可以了，这个是典型的问题，不用游标的。

[Felixzhaowenzhong]

IF EXISTS ( SELECT  * FROM sysobjects WHERE id = OBJECT_ID('[two]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1 ) 
    DROP TABLE two 
CREATE TABLE two
(id INT IDENTITY(1, 1)NOT NULL ,
 UserName VARCHAR(50) ,
 Code VARCHAR(50) NULL
) 
 
SET IDENTITY_INSERT two ON 
INSERT  two( id, UserName, code )VALUES  ( 1, 'User1', 'A' ) 
INSERT  two( id, UserName, code )VALUES  ( 1, 'User1', 'B' ) 
INSERT  two( id, UserName, code )VALUES  ( 1, 'User2', 'C' ) 
INSERT  two( id, UserName, code )VALUES  ( 1, 'User2', 'D' ) 
INSERT  two( id, UserName, code )VALUES  ( 1, 'User2', 'E' ) 
INSERT  two( id, UserName, code )VALUES  ( 1, 'User3', 'F' ) 
INSERT  two( id, UserName, code )VALUES  ( 1, 'User3', 'G' ) 
INSERT  two( id, UserName, code )VALUES  ( 1, 'User3', 'H' ) 
INSERT  two( id, UserName, code )VALUES  ( 1, 'User3', 'I' ) 
SET IDENTITY_INSERT two OFF 

--按某一列出结果的
SELECT  B.username ,LEFT(UserList, LEN(UserList) - 1) AS list
FROM    ( SELECT    username ,
                            ( SELECT code + ',' FROM two WHERE username = A.username ORDER BY  ID FOR XML PATH('')
                            ) AS UserList
          FROM two A GROUP BY  username
        ) B
        
/*
username	list
User1	A,B
User2	C,D,E

User3	F,G,H,I*/

[Felixzhaowenzhong]

--最通用的方式
declare @tb table (id int, value varchar(10)) 
insert into @tb values(1, 'aa') 
insert into @tb values(1, 'bb') 
insert into @tb values(2, 'aaa') 
insert into @tb values(2, 'bbb') 
insert into @tb values(2, 'ccc')

select id , [value]=
stuff((select ','+[value] from @tb t where id =tv.id for xml path('')), 1, 1, '') 
from @tb as tv
group by id 
/*
id	ccname
1	aa,bb
2	aaa,bbb,ccc
*/

[Felixzhaowenzhong]

--最通用的方式
declare @tb table (id int, value varchar(10)) 
insert into @tb values(1, 'aa') 
insert into @tb values(1, 'bb') 
insert into @tb values(2, 'aaa') 
insert into @tb values(2, 'bbb') 
insert into @tb values(2, 'ccc')

select id , [value]=
stuff((select ','+[value] from @tb t where id =tv.id for xml path('')), 1, 1, '') 
from @tb as tv
group by id 
/*
id	ccname
1	aa,bb
2	aaa,bbb,ccc
*/

[JB-Zhang]

不是呀,AA,AB,AC是一個字段的值(AA,AB與AC拼接成字符串),並不是三個字段呀.

[kevinjay567]

xml path