27,582
社区成员




--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[sd] nvarchar(21),[zl] nvarchar(22),[sl] int)
Insert #T
select 1,N'A',N'苹果',3 union all
select 2,N'A',N'西瓜',1 union all
select 3,N'B',N'西瓜',6 union all
select 4,N'B',N'柚子',5 union all
select 5,N'C',N'梨子',3 union all
select 6,N'C',N'西瓜',7 union all
select 7,N'C',N'苹果',4
Go
--测试数据结束
SELECT * from #T
Go
SELECT sd ,
STUFF(( SELECT ',' + zl+','+RTRIM(sl)
FROM #T
WHERE #T.sd = a.sd
FOR
XML PATH('')
), 1, 1, '') AS zl
FROM #T a
GROUP BY a.sd
drop table #T;
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[sd] nvarchar(21),[zl] nvarchar(22),[sl] int,[xs] int)
Insert #T
select 1,N'A',N'苹果',3,1 union all
select 2,N'A',N'西瓜',1,2 union all
select 3,N'B',N'西瓜',6,3 union all
select 4,N'B',N'柚子',5,4 union all
select 5,N'C',N'梨子',3,4 union all
select 6,N'C',N'西瓜',7,2 union all
select 7,N'C',N'苹果',4,3
Go
--测试数据结束
SELECT sd ,
STUFF(( SELECT ',' + zl + ',' + RTRIM(sl)
FROM #T
WHERE #T.sd = a.sd
FOR
XML PATH('')
), 1, 1, '') AS MX ,
STUFF(( SELECT ',' + zl + ',' + RTRIM(xs)
FROM #T
WHERE #T.sd = a.sd
FOR
XML PATH('')
), 1, 1, '') AS XS
FROM #T a
GROUP BY a.sd
Create table #T([id] int,[sd] nvarchar(21),[zl] nvarchar(22),[sl] int,[xs] int)
Insert #T
select 1,N'A',N'苹果',3,1 union all
select 2,N'A',N'西瓜',1,2 union all
select 3,N'B',N'西瓜',6,3 union all
select 4,N'B',N'柚子',5,4 union all
select 5,N'C',N'梨子',3,4 union all
select 6,N'C',N'西瓜',7,2 union all
select 7,N'C',N'苹果',4,3
Go
Create table #T([id] int,[sd] nvarchar(21),[zl] nvarchar(22),[sl] int,[xs] int)
Insert #T
select 1,N'A',N'苹果',3,1 union all
select 2,N'A',N'西瓜',1,2 union all
select 3,N'B',N'西瓜',6,3 union all
select 4,N'B',N'柚子',5,4 union all
select 5,N'C',N'梨子',3,4 union all
select 6,N'C',N'西瓜',7,2 union all
select 7,N'C',N'苹果',4,3
GO
--测试数据结束
SELECT sd,stuff((
SELECT ','+cast(a.zl AS VARCHAR(20))+','+cast(a.sl AS VARCHAR(20)) FROM #t a
WHERE c.sd=a.sd
FOR XML PATH('')
),1,1,'') AS MX,
stuff((
SELECT ','+cast(b.zl AS VARCHAR(20))+','+cast(b.xs AS VARCHAR(20))FROM #t b
WHERE c.sd=b.sd
FOR XML PATH('')
),1,1,'') AS XS
FROM #t c
GROUP BY sd
Create table #T([id] int,[sd] nvarchar(21),[zl] nvarchar(22),[sl] int,[xs] int)
Insert #T
select 1,N'A',N'苹果',3,1 union all
select 2,N'A',N'西瓜',1,2 union all
select 3,N'B',N'西瓜',6,3 union all
select 4,N'B',N'柚子',5,4 union all
select 5,N'C',N'梨子',3,4 union all
select 6,N'C',N'西瓜',7,2 union all
select 7,N'C',N'苹果',4,3
Go
查询结果 希望是这样
SD MX XS
A 苹果,3,西瓜,1 苹果,1,西瓜,2
B 西瓜,6,柚子,5 西瓜,3,柚子,4
C 梨子,3,西瓜,7,苹果,4 梨子,4,西瓜,2,苹果,3 --测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[sd] nvarchar(21),[zl] nvarchar(22),[sl] int)
Insert #T
select 1,N'A',N'苹果',3 union all
select 2,N'A',N'西瓜',1 union all
select 3,N'B',N'西瓜',6 union all
select 4,N'B',N'柚子',5 union all
select 5,N'C',N'梨子',3 union all
select 6,N'C',N'西瓜',7 union all
select 7,N'C',N'苹果',4
Go
--测试数据结束
SELECT sd ,
STUFF(( SELECT ',' + zl+','+RTRIM(sl)
FROM #T
WHERE #T.sd = a.sd
FOR
XML PATH('')
), 1, 1, '') AS zl
FROM #T a
GROUP BY a.sd