declare @table table(id int ,num int ,[type] nvarchar(50))insert @table select 1,10,N'笔记本电脑' union all select 1,20,N'打印机' union all select 2,10,N'台式电脑' select ID ,texts=stuff(( select ','+[type]+'('+cast (num as varchar)+N')台' from @table tt where tt.id=t.id for xml path('')),1,1,'') from @table t group by id/* ID texts 1 笔记本电脑(10)台,打印机(20)台 2 台式电脑(10)台 */
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp; go create table #temp( [id] varchar(100), [num] varchar(100), [type] varchar(100)); insert #temp select '1','10','笔记本电脑' union all select '1','20','打印机' union all select '2','10','台式电脑' --SQL: ;WITH cte AS ( select id, [text] = [type]+'('+LTRIM(num)+')' from #temp ) SELECT id, [text]=STUFF((SELECT ','+[text] FROM cte b WHERE b.id=a.id FOR XML PATH('')),1,1,'') FROM cte a GROUP BY id /* id text 1 笔记本电脑(10),打印机(20) 2 台式电脑(10) */
更改下 "台" 位置放错了 select ID ,texts=stuff(( select ','+[type]+'('+cast (num as varchar)+N'台)' from @table tt where tt.id=t.id for xml path('')),1,1,'') from @table t group by id/* ID texts 1 笔记本电脑(10台),打印机(20台) 2 台式电脑(10台) */
--少了个“台”字儿 ;WITH cte AS ( select id, [text] = [type]+'('+LTRIM(num)+'台)' from #temp ) SELECT id, [text]=STUFF((SELECT ','+[text] FROM cte b WHERE b.id=a.id FOR XML PATH('')),1,1,'') FROM cte a GROUP BY id
;with tb as ( select 1 as ID,10 as num,'笔记本电脑' as type union select 1,20,'打印机' union select 2,10,'台式电脑' ) select ID, stuff((select ','+type+'('+CAST(num as varchar)+')' from tb b where b.ID=a.ID for xml path('')),1,1,'') 'name' from tb a group by ID/* 1 笔记本电脑(10),打印机(20) 2 台式电脑(10) */
declare @table table(id int ,num int ,[type] nvarchar(50))insert @table
select 1,10,N'笔记本电脑' union all
select 1,20,N'打印机' union all
select 2,10,N'台式电脑'
select ID
,texts=stuff((
select ','+[type]+'('+cast (num as varchar)+N')台'
from @table tt
where tt.id=t.id
for xml path('')),1,1,'')
from @table t
group by id/*
ID texts
1 笔记本电脑(10)台,打印机(20)台
2 台式电脑(10)台
*/
go
create table #temp( [id] varchar(100), [num] varchar(100), [type] varchar(100));
insert #temp
select '1','10','笔记本电脑' union all
select '1','20','打印机' union all
select '2','10','台式电脑' --SQL:
;WITH cte AS
(
select id, [text] = [type]+'('+LTRIM(num)+')' from #temp
)
SELECT id, [text]=STUFF((SELECT ','+[text] FROM cte b WHERE b.id=a.id FOR XML PATH('')),1,1,'')
FROM cte a
GROUP BY id
/*
id text
1 笔记本电脑(10),打印机(20)
2 台式电脑(10)
*/
"台" 位置放错了
select ID
,texts=stuff((
select ','+[type]+'('+cast (num as varchar)+N'台)'
from @table tt
where tt.id=t.id
for xml path('')),1,1,'')
from @table t
group by id/*
ID texts
1 笔记本电脑(10台),打印机(20台)
2 台式电脑(10台)
*/
;WITH cte AS
(
select id, [text] = [type]+'('+LTRIM(num)+'台)' from #temp
)
SELECT id, [text]=STUFF((SELECT ','+[text] FROM cte b WHERE b.id=a.id FOR XML PATH('')),1,1,'')
FROM cte a
GROUP BY id
( select 1 as ID,10 as num,'笔记本电脑' as type
union select 1,20,'打印机'
union select 2,10,'台式电脑'
)
select ID,
stuff((select ','+type+'('+CAST(num as varchar)+')' from tb b
where b.ID=a.ID
for xml path('')),1,1,'') 'name'
from tb a
group by ID/*
1 笔记本电脑(10),打印机(20)
2 台式电脑(10)
*/