问题描述:有表 TEST 如下:ID AA BB CC
1 ab saf asfc
1 cad asdf fasd
1 sd fsad sdfd
2 dadf dfss dfasd
2 asdf dfs sdfs
3 sdf sadf dfsaf
4 sdf sdfa sdaf
4 dsf ddssa sdaf
5 sadf sadf saf
5 saf asdf asf
5 asfd asdf saf
5 asdf sadf asdf
6 sadf ds sdfa
.
.
.需要查出来的数据是这样的格式:ID AA BB CC AA1 BB1 CC1 AA2 BB2 CC2 ...(有几个出来几个,以ID出现最多的为准吧)
1 ab saf asfc cad asdf fasd sd fsad sdfd
2 dadf dfss dfasd asdf dfs sdfs
3 sdf sadf dfsaf
4 sdf sdfa sdaf dsf ddssa sdaf
.
.
.
本来数据是在Excel有里面的,我把他导入SQL来处理了;当然如果能在Excel里面批量处理成需要的格式也是可以的,达到目的就行了;
1 ab saf asfc
1 cad asdf fasd
1 sd fsad sdfd
2 dadf dfss dfasd
2 asdf dfs sdfs
3 sdf sadf dfsaf
4 sdf sdfa sdaf
4 dsf ddssa sdaf
5 sadf sadf saf
5 saf asdf asf
5 asfd asdf saf
5 asdf sadf asdf
6 sadf ds sdfa
.
.
.需要查出来的数据是这样的格式:ID AA BB CC AA1 BB1 CC1 AA2 BB2 CC2 ...(有几个出来几个,以ID出现最多的为准吧)
1 ab saf asfc cad asdf fasd sd fsad sdfd
2 dadf dfss dfasd asdf dfs sdfs
3 sdf sadf dfsaf
4 sdf sdfa sdaf dsf ddssa sdaf
.
.
.
本来数据是在Excel有里面的,我把他导入SQL来处理了;当然如果能在Excel里面批量处理成需要的格式也是可以的,达到目的就行了;
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂整理人：中国风(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]')
生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]GO
动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/------------------------------------------------------------------------------------------
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+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78(8 行受影响)
*/
http://blog.csdn.net/feng19821209/archive/2006/06/22/821808.aspx
http://www.itwis.com/html/office/excel/20081104/2707.html
有时大家在制作表格时没有提前规划好，在表格做了很多后就需要进行行列转换。这里除了麻烦的用手工做外，还有没有好的办法呢。答案是有的。用手工又慢、效率奇低。而且容易出差错。
下面就分别说说EXCEL和WORD里的表格行列转换方法吧
先看看EXCEL的行列转换：首先很简单的实用的办法，例如你有一行数据1、2、3、4，你想变成1列、2列、3列、4列。那么这里很简单。先复制这一行。然后在列里进行选择性粘贴，注意这里最关键的地方是“转置”这里一定要勾上！
这是比较简单的一些转换，如果是一些复杂的就可以用函数、宏、VBA来实现，具体就得看要转换的实际情况
了。大家可以根据需要去编写相应宏、函数、VBA！
另外就是要灵活运用EXCEL的各种功能。再举个列子大家可以想想。
例如我有1、2、3。。.1000。总共是1到10000列。而我想变成一行100个。也就是说100＊100的矩形阵列。那就
不能用转置来完成了。 但是也不用VBA等来实现。哈。怎么做呢？假如你的数据在A列，你在B1输
入=A1，C1=A51然后都往下拖100，最后将A列隐藏起来。哈哈。是不是就成了。。很简单啊。但却实用。
你有想到吗？
再来说说WORD里的表格的行列转换。WORD里就没EXCEL的表格功能转换，那么怎么办呢。其一。你复制表格进去
EXCEL，根据上面的介绍的EXCEL的行列转换办法转换好。再复制回WORD。其二。用替换大法。主要是利用替换
在制表符、回车符等之间转换。明白了吧。具体我不多说。大家去实践。再提醒下，回车符是“^p”，“^t”
是制表符，其他符号大家自己去百度搜索吧。
create table tb(ID int,AA varchar(10),BB varchar(10),CC varchar(10))
insert into tb values(1 ,'ab' , 'saf' , 'asfc')
insert into tb values(1 ,'cad' , 'asdf' , 'fasd')
insert into tb values(1 ,'sd' , 'fsad' , 'sdfd')
insert into tb values(2 ,'dadf', 'dfss' , 'dfasd')
insert into tb values(2 ,'asdf', 'dfs' , 'sdfs')
insert into tb values(3 ,'sdf' , 'sadf' , 'dfsaf')
insert into tb values(4 ,'sdf' , 'sdfa' , 'sdaf')
insert into tb values(4 ,'dsf' , 'ddssa', 'sdaf')
insert into tb values(5 ,'sadf', 'sadf' , 'saf')
insert into tb values(5 ,'saf' , 'asdf' , 'asf')
insert into tb values(5 ,'asfd', 'asdf' , 'saf')
insert into tb values(5 ,'asdf', 'sadf' , 'asdf')
insert into tb values(6 ,'sadf', 'ds' , 'sdfa')
go--如果你能确定ID最多的有四个，则使用静态SQL。
select id ,
max(case px when 1 then aa else '' end) aa1,
max(case px when 1 then bb else '' end) bb1,
max(case px when 1 then cc else '' end) cc1,
max(case px when 2 then aa else '' end) aa2,
max(case px when 2 then bb else '' end) bb2,
max(case px when 2 then cc else '' end) cc2,
max(case px when 3 then aa else '' end) aa3,
max(case px when 3 then bb else '' end) bb3,
max(case px when 3 then cc else '' end) cc3,
max(case px when 4 then aa else '' end) aa4,
max(case px when 4 then bb else '' end) bb4,
max(case px when 4 then cc else '' end) cc4
from
(
select * , px = (select count(1) from tb where id = t.id and (aa < t.aa or (aa = t.aa and bb < t.bb) or (aa= t.aa and bb = t.bb and cc < t.cc))) + 1 from tb t
) m
group by id
/*
id aa1 bb1 cc1 aa2 bb2 cc2 aa3 bb3 cc3 aa4 bb4 cc4
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 ab saf asfc cad asdf fasd sd fsad sdfd
2 asdf dfs sdfs dadf dfss dfasd
3 sdf sadf dfsaf
4 dsf ddssa sdaf sdf sdfa sdaf
5 asdf sadf asdf asfd asdf saf sadf sadf saf saf asdf asf
6 sadf ds sdfa （所影响的行数为 6 行）
*/--如果你不能确定ID最多有多少个，则使用动态SQL。
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then aa else '''' end) [aa' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then bb else '''' end) [bb' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then cc else '''' end) [cc' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(1) from tb where id = t.id and (aa < t.aa or (aa = t.aa and bb < t.bb) or (aa= t.aa and bb = t.bb and cc < t.cc))) + 1 from tb t) m) as a
set @sql = @sql + ' from (select * , px = (select count(1) from tb where id = t.id and (aa < t.aa or (aa = t.aa and bb < t.bb) or (aa= t.aa and bb = t.bb and cc < t.cc))) + 1 from tb t) m group by id'
exec(@sql)
/*
id aa1 bb1 cc1 aa2 bb2 cc2 aa3 bb3 cc3 aa4 bb4 cc4
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 ab saf asfc cad asdf fasd sd fsad sdfd
2 asdf dfs sdfs dadf dfss dfasd
3 sdf sadf dfsaf
4 dsf ddssa sdaf sdf sdfa sdaf
5 asdf sadf asdf asfd asdf saf sadf sadf saf saf asdf asf
6 sadf ds sdfa */drop table tb
create table tb(ID int,AA varchar(10),BB varchar(10),CC varchar(10))
insert into tb values(1 ,'ab' , 'saf' , 'asfc')
insert into tb values(1 ,'cad' , 'asdf' , 'fasd')
insert into tb values(1 ,'sd' , 'fsad' , 'sdfd')
insert into tb values(2 ,'dadf', 'dfss' , 'dfasd')
insert into tb values(2 ,'asdf', 'dfs' , 'sdfs')
insert into tb values(3 ,'sdf' , 'sadf' , 'dfsaf')
insert into tb values(4 ,'sdf' , 'sdfa' , 'sdaf')
insert into tb values(4 ,'dsf' , 'ddssa', 'sdaf')
insert into tb values(5 ,'sadf', 'sadf' , 'saf')
insert into tb values(5 ,'saf' , 'asdf' , 'asf')
insert into tb values(5 ,'asfd', 'asdf' , 'saf')
insert into tb values(5 ,'asdf', 'sadf' , 'asdf')
insert into tb values(6 ,'sadf', 'ds' , 'sdfa')
go--如果你能确定ID最多的有四个，则使用静态SQL。
select id ,
max(case px when 1 then aa else '' end) aa1,
max(case px when 1 then bb else '' end) bb1,
max(case px when 1 then cc else '' end) cc1,
max(case px when 2 then aa else '' end) aa2,
max(case px when 2 then bb else '' end) bb2,
max(case px when 2 then cc else '' end) cc2,
max(case px when 3 then aa else '' end) aa3,
max(case px when 3 then bb else '' end) bb3,
max(case px when 3 then cc else '' end) cc3,
max(case px when 4 then aa else '' end) aa4,
max(case px when 4 then bb else '' end) bb4,
max(case px when 4 then cc else '' end) cc4
from
(
select * , px = row_number() over(partition by id order by aa , bb , cc) from tb t
) m
group by id
/*
id aa1 bb1 cc1 aa2 bb2 cc2 aa3 bb3 cc3 aa4 bb4 cc4
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 ab saf asfc cad asdf fasd sd fsad sdfd
2 asdf dfs sdfs dadf dfss dfasd
3 sdf sadf dfsaf
4 dsf ddssa sdaf sdf sdfa sdaf
5 asdf sadf asdf asfd asdf saf sadf sadf saf saf asdf asf
6 sadf ds sdfa (6 行受影响)
*/--如果你不能确定ID最多有多少个，则使用动态SQL。
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then aa else '''' end) [aa' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then bb else '''' end) [bb' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then cc else '''' end) [cc' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = row_number() over(partition by id order by aa , bb , cc) from tb t) m) as a
set @sql = @sql + ' from (select * , px = row_number() over(partition by id order by aa , bb , cc) from tb t) m group by id'
exec(@sql)
/*
id aa1 bb1 cc1 aa2 bb2 cc2 aa3 bb3 cc3 aa4 bb4 cc4
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 ab saf asfc cad asdf fasd sd fsad sdfd
2 asdf dfs sdfs dadf dfss dfasd
3 sdf sadf dfsaf
4 dsf ddssa sdaf sdf sdfa sdaf
5 asdf sadf asdf asfd asdf saf sadf sadf saf saf asdf asf
6 sadf ds sdfa (6 行受影响)
*/drop table tb
ID有快100行，列有快20列，SQL语句超过8000字符了，杂整？还有个列的数据是单位如：%,C,M2,千克，千克/M3，
经常有人提到,用动态生成SQL语句的方法处理数据时,处理语句超长,无法处理的问题
下面就讨论这个问题:
--邹建 2003.9(引用请保留此信息)--*/
/*-- 测试环境
--以系统表 syscolumns 为测试数据,要求按xtype为列字段,name为行字段,统计colid的和
--要求结果
xtype filedname_1 fieldname_2 ..... fieldname_n
-------- -------------- -------------- -------- --------------
34 0 0 ..... 1
--*//*--常规处理方法(不加行数限制会因生成的字符串益出而出错)
set rowcount 10 --因为syscolumns的记录较,会导致生成的字符串溢出,所以限制一下行数declare @s nvarchar(4000)
set @s=''
select @s=@s+N','+quotename([name])
+N'=sum(case name when '+quotename([name],'''')
+N' then [colid] else 0 end)'
from(select distinct [name] from [syscolumns]) aset rowcount 0 --取消限制
exec(N'select [xtype]'+@s+N' from [syscolumns] group by [xtype]')/*--问题
不加行数限制时,会因生成的字符串益出而出错
--*/
--*//*--方法1. 多个变量处理--根据查询结果定义变量(实际处理中,应该是估计需要多少个变量,定义足够多的变量个数,多定义变量并不影响处理,下面就多定义了一个)
--生成数据处理临时表
SELECT id=IDENTITY(int,0,1),
g=0,
a=CAST(N','+QUOTENAME([name])
+N'=SUM(CASE [name] WHEN N'+QUOTENAME(name,N'''')
+N' THEN [colid] ELSE 0 END)'
as nvarchar(4000))
INTO # FROM syscolumns
WHERE name>N''
GROUP BY name--分组临时表
UPDATE a SET G=id/i
FROM # a,(SELECT i=3800/MAX(LEN(a)) FROM #)b
SELECT MAX(g)+1 as N'需要的变量个数' FROM #DECLARE @0 nvarchar(4000),@1 nvarchar(4000),@2 nvarchar(4000),@3 nvarchar(4000),@4 nvarchar(4000)
SELECT @0=N'',@1=N'',@2=N'',@3=N'',@4=N''
SELECT
@0=CASE g WHEN 0 THEN @0+a ELSE @0 END,
@1=CASE g WHEN 1 THEN @1+a ELSE @1 END,
@2=CASE g WHEN 2 THEN @2+a ELSE @2 END,
@3=CASE g WHEN 3 THEN @3+a ELSE @3 END,
@4=CASE g WHEN 4 THEN @4+a ELSE @4 END
FROM #
EXEC(N'SELECT xtype'+@0+@1+@2+@3+@4+N' FROM syscolumns GROUP BY xtype')
DROP TABLE #/*--方法说明优点:比较灵活,数据量大时只需要增加变量就行了.不用改动其他部分
缺点:要自行估计处理的数据,估计不足就会出错
--*/
--*//*--方法2. bcp+isql--因为要用到bcp+isql,所以需要这些信息
declare @servername sysname,@username sysname,@pwd sysname
select @servername=@@servername --服务器名
,@username=N'' --用户名
,@pwd=N'' --密码declare @tbname sysname,@s nvarchar(4000)--创建数据处理临时表
set @tbname=quotename(N'##temp_'+cast(newid() as varchar(36)))
set @s=N'create table '+@tbname+'(a nvarchar(4000))
insert into '+@tbname+N'
select N''create view '
+stuff(@tbname,2,2,N'')
+N' as
select [xtype]''
union all
select N'',''+quotename([name])+''=sum(case [name] when N''
+quotename([name],'''''''')
+'' then [colid] else 0 end)''
from(select distinct [name] from [syscolumns] where name<>N''xtype'')a
union all
select N''from [syscolumns] group by [xtype]'''
exec(@s)--生成创建视图的文件,注意使用了文件:c:\temp.txt
set @s=N'bcp "'+@tbname+N'" out "c:\'+@tbname+N'" /S"'
+@servername+N'" /U"'+@username+N'" /P"'+@pwd+N'" /w'
exec master..xp_cmdshell @s,no_output--调用isql生成数据处理视图
set @s=N'osql /S"'+@servername
+case
when @username=N'' then N'" /E'
else N'" /U"'+@username+N'" /P"'+@pwd+N'"'
end
+N' /d"'+db_name()+N'" /i"c:\'+@tbname+'"'
exec master..xp_cmdshell @s,no_output--删除临时文件
set @s=N'del "c:\'+@tbname+'"'
exec master..xp_cmdshell @s,no_output--调用视图,显示处理结果
set @s=N'drop table '+@tbname+N'
select * from '+stuff(@tbname,2,2,N'')+N'
drop view '+stuff(@tbname,2,2,N'')
exec(@s)/*--方法总结 优点:程序自动处理,不存在判断错误的问题
缺点:复杂,经过的步骤多,容易出错,而且需要一定的操作员权限
--*/
--*/--/*-- 方法3. 多个变量处理,综合了方法1,2的优点,解决了方法1中需要人为判断,增加变量的问题,排除了方法2,需要权限和过程复杂的问题
DECLARE @sqlhead nvarchar(4000),@sqlend nvarchar(4000)
,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
,@i int,@ic nvarchar(10)--生成数据处理临时表
SELECT id=IDENTITY(int,0,1),
g=0,
a=CAST(N','
+QUOTENAME([name])
+N'=SUM(CASE [name] WHEN N'
+QUOTENAME(name,N'''')
+N' THEN [colid] ELSE 0 END)'
as nvarchar(4000))
INTO # FROM(
SELECT DISTINCT name FROM [syscolumns] WHERE name>N'')a--分组临时表
UPDATE a SET @i=id/i,g=@i
FROM # a,(SELECT i=3800/MAX(LEN(a)) FROM #)b
SET @ic=@i--生成数据处理语句
SELECT
@sqlhead=N''''
+REPLACE(N'SELECT [xtype]',N'''',N'''''')
+'''',
@sqlend=N''''
+REPLACE(N' FROM [syscolumns] GROUP BY [xtype]',N'''',N'''''')
+N'''',
@sql1=N'',@sql2=N'',@sql3=N'',@sql4=N''
WHILE @ic>=0
SELECT
@sql1=N',@'+@ic+N' nvarchar(4000)'+@sql1,
@sql2=N',@'+@ic+N'=N'''''+@sql2,
@sql3=N',@'+@ic
+N'=CASE g WHEN '+@ic
+N' THEN @'+@ic+N'+a ELSE @'+@ic
+N' END'+@sql3,
@sql4=N'+@'+@ic+@sql4,
@ic=@ic-1
SELECT
@sql1=STUFF(@sql1,1,1,N''),
@sql2=STUFF(@sql2,1,1,N''),
@sql3=STUFF(@sql3,1,1,N''),
@sql4=STUFF(@sql4,1,1,N'')--执行
EXEC(N'DECLARE '+@sql1+N'
SELECT '+@sql2+N'
SELECT '+@sql3+N' FROM #
EXEC(N'+@sqlhead+N'+'+@sql4+N'+N'+@sqlend+N')')
--删除临时表
DROP TABLE #/*--方法总结
总结了前两种方法的优点，自动判断需要处理的变量数
--*/
--*/