那个写了,可能你没看到,重写贴下--原数据
declare @t table(
province varchar(10),
number int
)insert @t select
'AAA' , 1
union all select
'BBB' , 2
union all select
'CCC' , 4
union all select
'DDD' , 19
union all select
'EEE' , 12
union all select
'FFF' , 4
union all select
'GGG' , 5
union all select
'HHH' , 6
union all select
'JJJ' , 7
union all select
'合计', 79 --插入临时表
select IDENTITY(int,0,1) as Id,*
into #t
from @t
order by provinceselect a,b,c,d
from (
select max(case when id % 4=0 then province end) as a,
max(case when id % 4=1 then province end) as b,
max(case when id % 4=2 then province end) as c,
max(case when id % 4=3 then province end) as d,
id/4 as id,
1 as id1
from #t
group by id/4
union all
select max(case when id % 4=0 then cast(number as varchar) end),
max(case when id % 4=1 then cast(number as varchar) end),
max(case when id % 4=2 then cast(number as varchar) end),
max(case when id % 4=3 then cast(number as varchar) end),
id/4 as id,
2 as id1
from #t
group by id/4
) as t
order by id,id1drop table #t--结果
a b c d
------------------------------ ------------------------------ ------------------------------ ------------------------------
AAA BBB CCC DDD
1 2 4 19
EEE FFF GGG HHH
12 4 5 6
JJJ 合计 NULL NULL
7 79 NULL NULL(所影响的行数为 6 行)警告: 聚合或其它 SET 操作消除了空值。
declare @t table(
province varchar(10),
number int
)insert @t select
'AAA' , 1
union all select
'BBB' , 2
union all select
'CCC' , 4
union all select
'DDD' , 19
union all select
'EEE' , 12
union all select
'FFF' , 4
union all select
'GGG' , 5
union all select
'HHH' , 6
union all select
'JJJ' , 7
union all select
'合计', 79 --插入临时表
select IDENTITY(int,0,1) as Id,*
into #t
from @t
order by provinceselect a,b,c,d
from (
select max(case when id % 4=0 then province end) as a,
max(case when id % 4=1 then province end) as b,
max(case when id % 4=2 then province end) as c,
max(case when id % 4=3 then province end) as d,
id/4 as id,
1 as id1
from #t
group by id/4
union all
select max(case when id % 4=0 then cast(number as varchar) end),
max(case when id % 4=1 then cast(number as varchar) end),
max(case when id % 4=2 then cast(number as varchar) end),
max(case when id % 4=3 then cast(number as varchar) end),
id/4 as id,
2 as id1
from #t
group by id/4
) as t
order by id,id1drop table #t--结果
a b c d
------------------------------ ------------------------------ ------------------------------ ------------------------------
AAA BBB CCC DDD
1 2 4 19
EEE FFF GGG HHH
12 4 5 6
JJJ 合计 NULL NULL
7 79 NULL NULL(所影响的行数为 6 行)警告: 聚合或其它 SET 操作消除了空值。
insert tt select 1, 2 , 4 , 19 , 12 , 42go
--select colid,name from syscolumns where id = object_id('tt')
declare @s varchar(8000)
set @s = ''
select @s = @s + ' select '+ltrim(colid - 1)+' as colid ,ltrim('+name + ') as name from tt union all'
from syscolumns
where id = object_id('tt')
set @s = left(@s, len(@s) - 9)
exec('select * into tmp from ('+ @s +') a')
select a,b,c,d from(
select cast(max(case when colid % 4=0 then name end) as varchar) as a,
cast(max(case when colid % 4=1 then name end) as varchar) as b,
cast(max(case when colid % 4=2 then name end) as varchar) as c,
cast(max(case when colid % 4=03 then name end) as varchar) as d,
colid/4 as id,
1 as id1
from (select colid - 1 as colid,name from syscolumns where id = object_id('tt') ) fgroup by colid/4
unionselect max(case when colid % 4=0 then name end),
max(case when colid % 4=1 then name end),
max(case when colid % 4=2 then name end),
max(case when colid % 4=3 then name end),
colid/4 as id,
2 as id1
from tmp
group by colid/4) b
order by id,id1drop table tt,tmp/*
a b c d
------------------------------ ------------------------------ ------------------------------ ------------------------------
AAA BBB CCC DDD
1 2 4 19
EEE 合计 NULL NULL
12 42 NULL NULL(所影响的行数为 4 行)
*/
不需要临时表。
declare @s varchar(8000),@s1 varchar(1000),@s2 varchar(1000),@tb varchar(255),@cc int,@s3 varchar(1000)
select @s1 =',',@s2=',',@tb = 't_line',@cc = 2,@s = ''
select @s3 = isnull(@s3+',','')+'a'+rtrim(colid) from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb) and colid <= @cc order by colid
select @s = case @s when '' then '' else @s +'union ' end+case when @s1 <>',' and @s2 <> ',' and colid%@cc = 1
then 'select '+rtrim(colid*10)+' zzid,'+rtrim(stuff(@s1,1,1,''))
+' union select '+rtrim(colid*10+1)+' zzid,'+rtrim(stuff(@s2,1,1,''))+' from '+@tb else '' end,
@s1 = case when colid%@cc = 1 then '' else @s1 end + ','''+name+''' a'+rtrim(colid),
@s2 = case when colid%@cc = 1 then '' else @s2 end + ',convert(varchar(20),'+name+') a'+rtrim(colid)
from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb) order by colid
select @cc = @cc-count(*)%@cc from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb)
if @cc <> 0
set @s = rtrim(@s)+' union select 2560 zzid,'+rtrim(stuff(@s1,1,1,''))+REPLICATE(',''''',@cc)
+' union select 2561 zzid,'+rtrim(stuff(@s2,1,1,''))++REPLICATE(',''''',@cc)
+' from '+@tb
set @s = 'select '+@s3+' from ('+@s+') b order by zzid'
select @s3,@s
exec(@s)
修改后,当设置的列数大于表中的列数时,直接取表中的列数。
declare @s varchar(8000),@s1 varchar(1000),@s2 varchar(1000),@tb varchar(255),@cc int,@s3 varchar(1000),@c int
select @s1 =',',@s2=',',@tb = 't_line',@cc = 10,@s = ''
select @c = count(*) from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb)
if @c < @cc set @cc = @c
select @s3 = isnull(@s3+',','')+'a'+rtrim(colid) from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb) and colid <= @cc order by colid
select @s = case @s when '' then '' else @s +'union ' end+case when @s1 <>',' and @s2 <> ',' and colid%@cc = 1
then 'select '+rtrim(colid*10)+' zzid,'+rtrim(stuff(@s1,1,1,''))
+' union select '+rtrim(colid*10+1)+' zzid,'+rtrim(stuff(@s2,1,1,''))+' from '+@tb else '' end,
@s1 = case when colid%@cc = 1 then '' else @s1 end + ','''+name+''' a'+rtrim(colid),
@s2 = case when colid%@cc = 1 then '' else @s2 end + ',convert(varchar(20),'+name+') a'+rtrim(colid)
from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb) order by colid
select @cc = @cc-@c%@cc
if @cc <> 0
set @s = case @s when '' then '' else @s+' union' end +' select 2560 zzid,'+rtrim(stuff(@s1,1,1,''))+REPLICATE(',''''',@cc%@c)
+' union select 2561 zzid,'+rtrim(stuff(@s2,1,1,''))++REPLICATE(',''''',@cc%@c)
+' from '+@tb
set @s = 'select '+@s3+' from ('+@s+') b order by zzid'
select @s3,@s
exec(@s)
select @s1 =',',@s2=',',@tb = 'pbcatvld',@cc = 2,@s = ''
select @c = count(*) from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb)
if @c < @cc set @cc = @c
select @s3 = isnull(@s3+',','')+'a'+rtrim(colid) from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb) and colid <= @cc order by colid
select @s = case when @s1 <>',' and @s2 <> ',' and colid%@cc = 1
then case @s when '' then '' else @s +' union ' end+'select '+rtrim(colid*10)+' zzid,'+rtrim(stuff(@s1,1,1,''))
+' union select '+rtrim(colid*10+1)+' zzid,'+rtrim(stuff(@s2,1,1,''))+' from '+@tb else @s end,
@s1 = case when colid%@cc = 1 then '' else @s1 end + ','''+name+''' a'+rtrim(colid),
@s2 = case when colid%@cc = 1 then '' else @s2 end + ',convert(varchar(20),'+name+') a'+rtrim(colid)
from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb) order by colid
select @cc = @cc-@c%@cc
if @cc <> 0
set @s = case @s when '' then '' else @s+' union' end +' select 2560 zzid,'+rtrim(stuff(@s1,1,1,''))+REPLICATE(',''''',@cc%@c)
+' union select 2561 zzid,'+rtrim(stuff(@s2,1,1,''))++REPLICATE(',''''',@cc%@c)
+' from '+@tb
set @s = 'select '+@s3+' from ('+@s+') b order by zzid'
select @s3,@s
exec(@s)
select @s1 =',',@s2=',',@tb = 'Orders',@cc = 20,@s = ''
select @c = count(*) from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb)
if @c < @cc set @cc = @c
select @s3 = isnull(@s3+',','')+'a'+rtrim(colid) from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb) and colid <= @cc order by colid
select @s = case when @s1 <>',' and @s2 <> ',' and colid%@cc = 1
then case @s when '' then '' else @s +' union ' end+'select '+rtrim(colid*10)+' zzid,'+rtrim(stuff(@s1,1,1,''))
+' union select '+rtrim(colid*10+1)+' zzid,'+rtrim(stuff(@s2,1,1,''))+' from '+@tb else @s end,
@s1 = case when colid%@cc = 1 then '' else @s1 end + ','''+name+''' a'+rtrim(colid),
@s2 = case when colid%@cc = 1 then '' else @s2 end + ',convert(varchar(20),'+name+') a'+rtrim(colid)
from dbo.syscolumns where id = (select id from dbo.sysobjects where name = @tb) order by colid
select @cc = (@cc - @c%@cc)%@cc
set @s = case @s when '' then '' else @s+' union' end +' select 2560 zzid,'+rtrim(stuff(@s1,1,1,''))+REPLICATE(',''''',@cc%@c)
+' union select 2561 zzid,'+rtrim(stuff(@s2,1,1,''))++REPLICATE(',''''',@cc%@c)
+' from '+@tb
set @s = 'select '+@s3+' from ('+@s+') b order by zzid'
print @s
exec(@s)