查看: 3679|回复: 13
|
T-SQL 想到头都大了
[复制链接]
|
|
各位大哥大姐,向大家求助/请教一下:-
原本的 Table data 如下:-
TableA
ID Group Member
1 A Adam
2 A Bob
3 A David
4 B Frank
5 B Henry
6 C Kevin
7 C Larry
8 C Mike
要把 result 变成:-
Group Member
A Adam, Bob, David
B Frank, Henry
C Kevin, Larry, Mike
我想到头都大了。。。
各位,有办法吗?
[ 本帖最后由 nemracweis 于 24-2-2009 01:56 PM 编辑 ] |
|
|
|
|
|
|
|
发表于 24-2-2009 01:09 PM
|
显示全部楼层
是要diplay 成酱还是要save table成酱? |
|
|
|
|
|
|
|
发表于 24-2-2009 01:10 PM
|
显示全部楼层
SELECT Group, GROUP_CONCAT(Member) AS Members FROM TableA
GROUP BY Group
可以的话Column Name避免使用Group |
|
|
|
|
|
|
|
楼主 |
发表于 24-2-2009 01:52 PM
|
显示全部楼层
原帖由 cyea 于 24-2-2009 01:10 PM 发表
SELECT Group, GROUP_CONCAT(Member) AS Members FROM TableA
GROUP BY Group
可以的话Column Name避免使用Group
谢谢你的回复。
试了以上的办法,行不通哩。
还有其他的办法吗?
Error Message:
'group_concat' is not a recognized function name.
我是用 MSSQL 的。 |
|
|
|
|
|
|
|
楼主 |
发表于 24-2-2009 01:55 PM
|
显示全部楼层
回复 2# 三月の摇摇 的帖子
要用来 display result 的。
总觉得是 T-SQL 才可以做到。
有什么办法可以用普通的 SQL Statement, 我不要用Stored Proc 哩。
[ 本帖最后由 nemracweis 于 24-2-2009 02:34 PM 编辑 ] |
|
|
|
|
|
|
|
发表于 25-2-2009 03:39 PM
|
显示全部楼层
回复 1# nemracweis 的帖子
SELECT Group, LEFT(Members , LEN(Members )-1) AS Member
FROM tableA AS extern
CROSS APPLY
(
SELECT Member + ','
FROM tableA AS intern
WHERE extern.Group =intern.Group
FOR XML PATH('')
) pre_trimmed (Members)
GROUP BY Group, Members; |
|
|
|
|
|
|
|
发表于 25-4-2009 01:23 PM
|
显示全部楼层
|
|
|
|
|
|
|
发表于 19-5-2009 11:33 PM
|
显示全部楼层
|
|
|
|
|
|
|
楼主 |
发表于 11-9-2009 11:27 AM
|
显示全部楼层
后来,看到这个很棒的 link :-
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
由于用着 MSSQL 2000 的关系,以下只适合小型的 query return value.
SELECT CategoryId,
MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END )
FROM ( SELECT p1.CategoryId, p1.ProductName,
( SELECT COUNT(*)
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
AND p2.ProductName <= p1.ProductName )
FROM Northwind.dbo.Products p1 ) D ( CategoryId, ProductName, seq )
GROUP BY CategoryId ;
[ 本帖最后由 nemracweis 于 11-9-2009 11:29 AM 编辑 ] |
|
|
|
|
|
|
|
发表于 13-9-2009 05:04 PM
|
显示全部楼层
回复 1# nemracweis 的帖子
试试看在Google搜索SQL Coalesce append string. 应该可以帮到你。 |
|
|
|
|
|
|
|
发表于 27-7-2010 02:44 PM
|
显示全部楼层
本帖最后由 html 于 27-7-2010 02:47 PM 编辑
select [Group],stuff
((select ', ' + rtrim(c.[Member])
from TableA c
where TableA.[Group] = c.[Group]
order by c.[Member]
for xml path('')
),1,1,'') AS Member
from TableA
group by [Group] |
|
|
|
|
|
|
|
发表于 28-7-2010 12:03 AM
|
显示全部楼层
本帖最后由 兔仙人 于 28-7-2010 12:05 AM 编辑
T-SQL 可以 寫 FUNCTION 嗎 ?
如果 可以 , 這個應該可以 幫到你
create or replace
FUNCTION "FN_CONCAT_ALL" (
ctx IN OT_CONCAT_EXPR)
RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
AGGREGATE USING OT_CONCAT_ALL; |
|
|
|
|
|
|
|
发表于 19-11-2010 05:00 AM
|
显示全部楼层
use master
go
if exists(
select name from sys.databases where name = N'test3'
)drop database test3
go
create database test3
go
use test3
go
if object_id(N'tabletest3',N'U') is not null
drop table tabletest3
go
create table tabletest3(
MemberID int identity(1,1) not null,
Mgroup nvarchar(2),
Mname nvarchar(20)
)
go
insert into tabletest3(Mgroup,Mname)
values(N'A',N'Adam'),
(N'A',N'Bob'),
(N'A',N'David'),
(N'B',N'Frank'),
(N'B',N'Henry'),
(N'C',N'Kevin'),
(N'C',N'Larry'),
(N'C',N'Mike')
go
if object_id(N'temptabletest3',N'U') is not null
drop table temptabletest3
go
create table temptabletest3(
MemberID int identity(1,1) not null,
Mgroup nvarchar(2),
Mname nvarchar(Max)
)
go
insert into temptabletest3(Mgroup)
select Mgroup from tabletest3
where not(Mgroup is null)
group by Mgroup
go
select * from temptabletest3
declare
@Mgroup nvarchar(max),
@Mname nvarchar(max),
@MCnt int
set @MCnt = (select Min(MemberID) from temptabletest3)
set @Mgroup = (select min(Mgroup) from temptabletest3 where MemberID = @MCnt)
while @Mgroup is not null
begin
set @Mname = null
select @Mname =
case
when @Mname is null then ''
else @Mname + N', '
end
+ t.Mname
from tabletest3 t
where Mgroup = @Mgroup
update temptabletest3
set Mname = @Mname
where Mgroup = @Mgroup
set @MCnt = (select MIN(MemberID) from temptabletest3 where @MCnt < MemberID)
set @Mgroup = (select Min(Mgroup) from temptabletest3 where MemberID = @MCnt)
end
select Mgroup as [Group],Mname as [Name] from temptabletest3
go |
|
|
|
|
|
|
|
发表于 21-12-2010 07:09 PM
|
显示全部楼层
Select [Group], LEFT(Member, LEN(Member) -1) as Member
From
(SELECT [Group],
(SELECT Member + ','
FROM dbo.QA p2
WHERE p2.[Group] = p1.[Group]
ORDER BY Member
FOR XML PATH('')) AS Member
FROM QA p1
GROUP BY [Group]) G |
|
|
|
|
|
|
| |
本周最热论坛帖子
|