27,579
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [字段二] varchar(100), [字段三] varchar(100));
insert #temp
select '值1','批文1' union all
select '值2','批文1' union all
select '值3','批文1' union all
select '值4','批文1' union all
select '值5','批文1' union all
select '值6','批文1' union all
select '值7','批文1' union all
select '值8','批文2' union all
select '值9','批文2' union all
select '值10','批文2' union all
select '值11','批文2' union all
select '值12','批文3' union all
select '值13','批文3'
--SQL
DECLARE @sql NVARCHAR(MAX), @colList NVARCHAR(MAX)
SET @colList = STUFF((select DISTINCT ','+QUOTENAME(ROW_NUMBER() OVER(PARTITION BY 字段三 ORDER BY GETDATE())) from #temp FOR XML PATH('')),1,1,'')
SET @sql = N'
select * from
(select rowid=ROW_NUMBER() OVER(PARTITION BY 字段三 ORDER BY GETDATE()), 字段二, 字段三 from #temp) a
pivot
(max(字段二) for rowid in('+ @colList +')) b
'
EXEC(@sql)
/*
字段三 1 2 3 4 5 6 7
批文1 值1 值2 值3 值4 值5 值6 值7
批文2 值8 值9 值10 值11 NULL NULL NULL
批文3 值12 值13 NULL NULL NULL NULL NULL
*/
SELECT A.[字段三], name = STUFF(
(SELECT ','+[字段二] FROM [Table_3] B
WHERE B.[字段三]=A.[字段三] ORDER BY [字段二] DESC FOR XML PATH('')),1,1,'')
FROM [Table_3] A GROUP BY [字段三]
请将表名换为你的表名即可
if object_id('Tempdb..#a') is not null drop table #a
create table #a([字段二] nvarchar(100) null, [字段三] nvarchar(100) null)
Insert Into #a
select '值1','批文1' union all
select '值2','批文1' union all
select '值3','批文1' union all
select '值4','批文1' union all
select '值5','批文1' union all
select '值6','批文1' union all
select '值7','批文1' union all
select '值8','批文2' union all
select '值9','批文2' union all
select '值10','批文2' union all
select '值11','批文2' union all
select '值12','批文3' union all
select '值13','批文3'
select t.字段三,
stuff((select ','+字段二 from #a z where t.字段三=z.字段三 for xml path('')), 1, 1, '') '字段二'
from #a t
group by t.字段三
-------------
(13 行受影响)
字段三 字段二
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
批文1 值1,值2,值3,值4,值5,值6,值7
批文2 值8,值9,值10,值11
批文3 值12,值13
(3 行受影响)