--创建数据表
CREATE TABLE [dbo].[Tb1](
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[BB] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[CC] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--插入数据值
insert into Tb1(AA,BB,CC) values('王1','A1','C1')
insert into Tb1(AA,BB,CC) values('王2','A2','C2')
insert into Tb1(AA,BB,CC) values('王3','A3','C3')
insert into Tb1(AA,BB,CC) values('王4','A4','C4')
insert into Tb1(AA,BB,CC) values('王5','A5','C5')
insert into Tb1(AA,BB,CC) values('王6','A6','C6')
insert into Tb1(AA,BB,CC) values('王7','A7','C7')
insert into Tb1(AA,BB,CC) values('王8','A8','C8')
insert into Tb1(AA,BB,CC) values('王9','A9','C9')
insert into Tb1(AA,BB,CC) values('王10','A10','C10')
insert into Tb1(AA,BB,CC) values('王11','A11','C11')
insert into Tb1(AA,BB,CC) values('王12','A12','C12')
insert into Tb1(AA,BB,CC) values('王13','A13','C13')
原始状态:
查询后显示的状态:
高手们指点哈。
CREATE TABLE [dbo].[Tb1](
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[BB] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[CC] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--插入数据值
insert into Tb1(AA,BB,CC) values('王01','A1','C1')
insert into Tb1(AA,BB,CC) values('王02','A2','C2')
insert into Tb1(AA,BB,CC) values('王03','A3','C3')
insert into Tb1(AA,BB,CC) values('王04','A4','C4')
insert into Tb1(AA,BB,CC) values('王05','A5','C5')
insert into Tb1(AA,BB,CC) values('王06','A6','C6')
insert into Tb1(AA,BB,CC) values('王07','A7','C7')
insert into Tb1(AA,BB,CC) values('王08','A8','C8')
insert into Tb1(AA,BB,CC) values('王09','A9','C9')
insert into Tb1(AA,BB,CC) values('王10','A10','C10')
insert into Tb1(AA,BB,CC) values('王11','A11','C11')
insert into Tb1(AA,BB,CC) values('王12','A12','C12')
insert into Tb1(AA,BB,CC) values('王13','A13','C13')
insert into Tb1(AA,BB,CC) values('王14','A14','C14')
goselect max(case (px-1)/n.cnt when 0 then aa else '' end) AA1,
max(case (px-1)/n.cnt when 0 then bb else '' end) BB1,
max(case (px-1)/n.cnt when 0 then cc else '' end) CC1,
max(case (px-1)/n.cnt when 1 then aa else '' end) AA2,
max(case (px-1)/n.cnt when 1 then bb else '' end) BB2,
max(case (px-1)/n.cnt when 1 then cc else '' end) CC2,
max(case (px-1)/n.cnt when 2 then aa else '' end) AA3,
max(case (px-1)/n.cnt when 2 then bb else '' end) BB3,
max(case (px-1)/n.cnt when 2 then cc else '' end) CC3
from
(
select t.* , px = (select count(1) from Tb1 where aa < t.aa) + 1 From Tb1 t
) m ,(select (case when count(1)%3 = 0 then count(1)/3 else count(1)/3+1 end) cnt from Tb1) n
group by (px-1)%n.cntdrop table Tb1/*
AA1 BB1 CC1 AA2 BB2 CC2 AA3 BB3 CC3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
王01 A1 C1 王06 A6 C6 王11 A11 C11
王02 A2 C2 王07 A7 C7 王12 A12 C12
王03 A3 C3 王08 A8 C8 王13 A13 C13
王04 A4 C4 王09 A9 C9 王14 A14 C14
王05 A5 C5 王10 A10 C10 (所影响的行数为 5 行)
*/
CREATE TABLE [dbo].[Tb1](
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[BB] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[CC] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--插入数据值
insert into Tb1(AA,BB,CC) values(N'王01','A1','C1')
insert into Tb1(AA,BB,CC) values(N'王02','A2','C2')
insert into Tb1(AA,BB,CC) values(N'王03','A3','C3')
insert into Tb1(AA,BB,CC) values(N'王04','A4','C4')
insert into Tb1(AA,BB,CC) values(N'王05','A5','C5')
insert into Tb1(AA,BB,CC) values(N'王06','A6','C6')
insert into Tb1(AA,BB,CC) values(N'王07','A7','C7')
insert into Tb1(AA,BB,CC) values(N'王08','A8','C8')
insert into Tb1(AA,BB,CC) values(N'王09','A9','C9')
insert into Tb1(AA,BB,CC) values(N'王10','A10','C10')
insert into Tb1(AA,BB,CC) values(N'王11','A11','C11')
insert into Tb1(AA,BB,CC) values(N'王12','A12','C12')
insert into Tb1(AA,BB,CC) values(N'王13','A13','C13')
insert into Tb1(AA,BB,CC) values(N'王14','A14','C14')
goselect max(case (px-1)/n.cnt when 0 then aa else '' end) AA1,
max(case (px-1)/n.cnt when 0 then bb else '' end) BB1,
max(case (px-1)/n.cnt when 0 then cc else '' end) CC1,
max(case (px-1)/n.cnt when 1 then aa else '' end) AA2,
max(case (px-1)/n.cnt when 1 then bb else '' end) BB2,
max(case (px-1)/n.cnt when 1 then cc else '' end) CC2,
max(case (px-1)/n.cnt when 2 then aa else '' end) AA3,
max(case (px-1)/n.cnt when 2 then bb else '' end) BB3,
max(case (px-1)/n.cnt when 2 then cc else '' end) CC3
from
(
select t.* , px = row_number() over(order by aa) From Tb1 t
) m ,(select (case when count(1)%3 = 0 then count(1)/3 else count(1)/3+1 end) cnt from Tb1) n
group by (px-1)%n.cntdrop table Tb1/*
AA1 BB1 CC1 AA2 BB2 CC2 AA3 BB3 CC3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
王01 A1 C1 王06 A6 C6 王11 A11 C11
王02 A2 C2 王07 A7 C7 王12 A12 C12
王03 A3 C3 王08 A8 C8 王13 A13 C13
王04 A4 C4 王09 A9 C9 王14 A14 C14
王05 A5 C5 王10 A10 C10 (5 行受影响)
*/
--创建数据表
CREATE TABLE [dbo].[Tb1](
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[BB] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[CC] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--插入数据值
insert into Tb1(AA,BB,CC) values('王1','A1','C1')
insert into Tb1(AA,BB,CC) values('王2','A2','C2')
insert into Tb1(AA,BB,CC) values('王3','A3','C3')
insert into Tb1(AA,BB,CC) values('王4','A4','C4')
insert into Tb1(AA,BB,CC) values('王5','A5','C5')
insert into Tb1(AA,BB,CC) values('王6','A6','C6')
insert into Tb1(AA,BB,CC) values('王7','A7','C7')
insert into Tb1(AA,BB,CC) values('王8','A8','C8')
insert into Tb1(AA,BB,CC) values('王9','A9','C9')
insert into Tb1(AA,BB,CC) values('王10','A10','C10')
insert into Tb1(AA,BB,CC) values('王11','A11','C11')
insert into Tb1(AA,BB,CC) values('王12','A12','C12')
insert into Tb1(AA,BB,CC) values('王13','A13','C13')
insert into Tb1(AA,BB,CC) values('王14','A14','C14');with t
as(
select NTILE(3)over(order by getdate()) TITLE,* from [dbo].[Tb1]
),
m as(
select ROW_NUMBER()over(partition by TITLE order by getdate()) as id ,
* from t
)
select a.AA,a.BB,a.CC,b.AA,b.BB,b.CC,isnull(c.AA,'')AA,
isnull(c.BB,'')BB,isnull(c.CC,'')CC from(
select * from m where TITLE=1)a
full join (select * from m where TITLE=2)b
on a.id=b.id
full join (select * from m where TITLE=3)c
on a.id=c.id
/*
AA BB CC AA BB CC AA BB CC
王1 A1 C1 王6 A6 C6 王11 A11 C11
王2 A2 C2 王7 A7 C7 王12 A12 C12
王3 A3 C3 王8 A8 C8 王13 A13 C13
王4 A4 C4 王9 A9 C9 王14 A14 C14
王5 A5 C5 王10 A10 C10
*/
不知道你是否需要?
这里转一下 中国风 的代码
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子,比较形象易懂
整理人:中国风(Roy)
日期:2008.06.06
******************************************************************************************************************************************************/
--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
http://blog.csdn.net/thankstar/article/details/5896481
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[BB] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[CC] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--插入数据值
insert into Tb1(AA,BB,CC) values('王01','A1','C1')
insert into Tb1(AA,BB,CC) values('王02','A2','C2')
insert into Tb1(AA,BB,CC) values('王03','A3','C3')
insert into Tb1(AA,BB,CC) values('王04','A4','C4')
insert into Tb1(AA,BB,CC) values('王05','A5','C5')
insert into Tb1(AA,BB,CC) values('王06','A6','C6')
insert into Tb1(AA,BB,CC) values('王07','A7','C7')
insert into Tb1(AA,BB,CC) values('王08','A8','C8')
insert into Tb1(AA,BB,CC) values('王09','A9','C9')
insert into Tb1(AA,BB,CC) values('王10','A10','C10')
insert into Tb1(AA,BB,CC) values('王11','A11','C11')
insert into Tb1(AA,BB,CC) values('王12','A12','C12')
insert into Tb1(AA,BB,CC) values('王13','A13','C13')
insert into Tb1(AA,BB,CC) values('王14','A14','C14')
godeclare @i as int
set @i = 2 --这里更改值,可变换显示列数declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then AA else '''' end) [AA' + cast(i+1 as varchar) + ']'
+ ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then BB else '''' end) [BB' + cast(i+1 as varchar) + ']'
+ ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then CC else '''' end) [CC' + cast(i+1 as varchar) + ']'
from (select number i from master..spt_values where type='p' and number < @i) as a
set @sql = 'select ' + substring(@sql,2,len(@sql)) + ' from (
select t.* , px = (select count(1) from Tb1 where aa < t.aa) + 1 From Tb1 t
) m ,(select (case when count(1)%'+cast(@i as varchar)+' = 0 then count(1)/'+cast(@i as varchar)+' else count(1)/'+cast(@i as varchar)+'+1 end) cnt from Tb1) n
group by (px-1)%n.cnt
'
exec(@sql) drop table Tb1/*
AA1 BB1 CC1 AA2 BB2 CC2
---------- ---------- ---------- ---------- ---------- ----------
王01 A1 C1 王08 A8 C8
王02 A2 C2 王09 A9 C9
王03 A3 C3 王10 A10 C10
王04 A4 C4 王11 A11 C11
王05 A5 C5 王12 A12 C12
王06 A6 C6 王13 A13 C13
王07 A7 C7 王14 A14 C14
*/CREATE TABLE [dbo].[Tb1](
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[BB] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[CC] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--插入数据值
insert into Tb1(AA,BB,CC) values('王01','A1','C1')
insert into Tb1(AA,BB,CC) values('王02','A2','C2')
insert into Tb1(AA,BB,CC) values('王03','A3','C3')
insert into Tb1(AA,BB,CC) values('王04','A4','C4')
insert into Tb1(AA,BB,CC) values('王05','A5','C5')
insert into Tb1(AA,BB,CC) values('王06','A6','C6')
insert into Tb1(AA,BB,CC) values('王07','A7','C7')
insert into Tb1(AA,BB,CC) values('王08','A8','C8')
insert into Tb1(AA,BB,CC) values('王09','A9','C9')
insert into Tb1(AA,BB,CC) values('王10','A10','C10')
insert into Tb1(AA,BB,CC) values('王11','A11','C11')
insert into Tb1(AA,BB,CC) values('王12','A12','C12')
insert into Tb1(AA,BB,CC) values('王13','A13','C13')
insert into Tb1(AA,BB,CC) values('王14','A14','C14')
godeclare @i as int
set @i = 3 --这里更改值,可变换显示列数declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then AA else '''' end) [AA' + cast(i+1 as varchar) + ']'
+ ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then BB else '''' end) [BB' + cast(i+1 as varchar) + ']'
+ ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then CC else '''' end) [CC' + cast(i+1 as varchar) + ']'
from (select number i from master..spt_values where type='p' and number < @i) as a
set @sql = 'select ' + substring(@sql,2,len(@sql)) + ' from (
select t.* , px = (select count(1) from Tb1 where aa < t.aa) + 1 From Tb1 t
) m ,(select (case when count(1)%'+cast(@i as varchar)+' = 0 then count(1)/'+cast(@i as varchar)+' else count(1)/'+cast(@i as varchar)+'+1 end) cnt from Tb1) n
group by (px-1)%n.cnt
'
exec(@sql) drop table Tb1/*
AA1 BB1 CC1 AA2 BB2 CC2 AA3 BB3 CC3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
王01 A1 C1 王06 A6 C6 王11 A11 C11
王02 A2 C2 王07 A7 C7 王12 A12 C12
王03 A3 C3 王08 A8 C8 王13 A13 C13
王04 A4 C4 王09 A9 C9 王14 A14 C14
王05 A5 C5 王10 A10 C10
*/
CREATE TABLE [dbo].[Tb1](
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[BB] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[CC] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--插入数据值
insert into Tb1(AA,BB,CC) values('王01','A1','C1')
insert into Tb1(AA,BB,CC) values('王02','A2','C2')
insert into Tb1(AA,BB,CC) values('王03','A3','C3')
insert into Tb1(AA,BB,CC) values('王04','A4','C4')
insert into Tb1(AA,BB,CC) values('王05','A5','C5')
insert into Tb1(AA,BB,CC) values('王06','A6','C6')
insert into Tb1(AA,BB,CC) values('王07','A7','C7')
insert into Tb1(AA,BB,CC) values('王08','A8','C8')
insert into Tb1(AA,BB,CC) values('王09','A9','C9')
insert into Tb1(AA,BB,CC) values('王10','A10','C10')
insert into Tb1(AA,BB,CC) values('王11','A11','C11')
insert into Tb1(AA,BB,CC) values('王12','A12','C12')
insert into Tb1(AA,BB,CC) values('王13','A13','C13')
insert into Tb1(AA,BB,CC) values('王14','A14','C14')
godeclare @i as int
set @i = 4 --这里更改值,可变换显示列数declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then AA else '''' end) [AA' + cast(i+1 as varchar) + ']'
+ ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then BB else '''' end) [BB' + cast(i+1 as varchar) + ']'
+ ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then CC else '''' end) [CC' + cast(i+1 as varchar) + ']'
from (select number i from master..spt_values where type='p' and number < @i) as a
set @sql = 'select ' + substring(@sql,2,len(@sql)) + ' from (
select t.* , px = (select count(1) from Tb1 where aa < t.aa) + 1 From Tb1 t
) m ,(select (case when count(1)%'+cast(@i as varchar)+' = 0 then count(1)/'+cast(@i as varchar)+' else count(1)/'+cast(@i as varchar)+'+1 end) cnt from Tb1) n
group by (px-1)%n.cnt
'
exec(@sql) drop table Tb1/*
AA1 BB1 CC1 AA2 BB2 CC2 AA3 BB3 CC3 AA4 BB4 CC4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
王01 A1 C1 王05 A5 C5 王09 A9 C9 王13 A13 C13
王02 A2 C2 王06 A6 C6 王10 A10 C10 王14 A14 C14
王03 A3 C3 王07 A7 C7 王11 A11 C11
王04 A4 C4 王08 A8 C8 王12 A12 C12 */