1. SQL行转列代码
-
-
表里面有这么一组数据:
code price
'MCU0-3' 15
'MCU0-3' 20
'MCU0-3' 22
'MCU3-15' 17
'MCU3-15' 16
'MCU3-15' -10
'MCU3-15' 50
'MCU3-27' 99
'MCU3-27' 96
'MCU3-27' 54
'MCU3-27' 14
'MCU3-27' 46
'MCU3-27' 86
现在需要得到这样的结果:
MCU0-3 MCU3-15 MCU3-27
15 17 99
20 16 96
22 -10 54
null 50 14
null null 46
null null 86
SQL语句是这样写的:
SELECT ID = IDENTITY(INT, 1, 1), price into #T1 from [table] where code = 'MCU0-3'
SELECT ID = IDENTITY(INT, 1, 1), price into #T2 from [table] where code = 'MCU3-15'
SELECT ID = IDENTITY(INT, 1, 1), price into #T3 from [table] where code = 'MCU3-27'
select t1.price as MCU0-3, t2.price as MCU3-15,t3.price as MCU3-27 from #T1 t1 FULL OUTER JOIN #T2 t2 on t1.id = t2.id FULL OUTER JOIN #T3 t3 on t2.id = t3.id
效率不高，但是这样写比较简单。用游标也能达到效果，但是效率怎么样我没有测试，我想应该不会比这个好多少。
2.
比如数据库中表内容如下：
id date month
001 12 1
001 12 2
001 12 3
001 15 4
001 28 5
001 12 6
001 12 7
001 12 8
001 12 9
001 15 10
001 28 11
001 12 12
002 12 1
003 12 2
004 12 3
005 15 4
006 28 5
007 12 6
008 12 7
009 12 8
010 12 9
011 15 10
012 28 11
013 12 12
说说关键代码：
SELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN date ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN date ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN date ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN date ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN date ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN date ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN date ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN date ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN date ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN date ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN date ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN date ELSE 0 END) AS '12月'
FROM stuInfo
GROUP BY ID
主要是用case 来转列，并用分组查询来判断表中的记录
常有人提到,在写行转列的功能用动态生成SQL语句的方法处理数据时,处理语句超长,会出现无法处理的问题
下面就讨论这个问题:
/*-- 数据测试环境 --*/
if exists (select * from dbo.sysobjects where id = object_id(N '[tb] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [tb]
GO
create table tb(单位名称 varchar(10),日期 datetime,销售额 int)
insert into tb
select 'A单位 ', '2001-01-01 ',100
union all select 'B单位 ', '2001-01-02 ',101
union all select 'C单位 ', '2001-01-03 ',102
union all select 'D单位 ', '2001-01-04 ',103
union all select 'E单位 ', '2001-01-05 ',104
union all select 'F单位 ', '2001-01-06 ',105
union all select 'G单位 ', '2001-01-07 ',106
union all select 'H单位 ', '2001-01-08 ',107
union all select 'I单位 ', '2001-01-09 ',108
union all select 'J单位 ', '2001-01-11 ',109
/*-- 要求结果
日期 A单位 B单位 C单位 D单位 E单位 F单位 G单位 H单位 I单位 J单位
---------- ----- ----- ----- ----- ----- ----- ---- ---- ---- ------
2001-01-01 100 0 0 0 0 0 0 0 0 0
2001-01-02 0 101 0 0 0 0 0 0 0 0
2001-01-03 0 0 102 0 0 0 0 0 0 0
2001-01-04 0 0 0 103 0 0 0 0 0 0
2001-01-05 0 0 0 0 104 0 0 0 0 0
2001-01-06 0 0 0 0 0 105 0 0 0 0
2001-01-07 0 0 0 0 0 0 106 0 0 0
2001-01-08 0 0 0 0 0 0 0 107 0 0
2001-01-09 0 0 0 0 0 0 0 0 108 0
2001-01-11 0 0 0 0 0 0 0 0 0 109
--*/
/*-- 常规处理方法*/
declare @sql varchar(8000)
set @sql= 'select 日期=convert(varchar(10),日期,120) '
select @sql=@sql+ ',[ '+单位名称
+ ']=sum(case 单位名称 when ' ' '+单位名称+ ' ' ' then 销售额 else 0 end) '
from(select distinct 单位名称 from tb) a
exec(@sql+ ' from tb group by convert(varchar(10),日期,120) ')
/*-- 问题: 如果单位很多,这时,@SQL的值就会被截断,从而出错.*/
/*--下面给出三种解决办法:--*/
--/*-- 方法1. 多个变量处理
--定义变量,估计需要多少个变量才能保存完所有数据
declare @sql0 varchar(8000),@sql1 varchar(8000)
--,...@sqln varchar(8000)
--生成数据处理临时表
select id=identity(int,0,1),groupid=0
,值= ',[ '+单位名称 + ']=sum(case 单位名称 when ' ' '
+单位名称+ ' ' ' then 销售额 else 0 end) '
into #temp from(select distinct 单位名称 from tb) a
--分组临时表,判断慨最多多少个单位可以组合成一个不超过8000的字符串,这里取假设为5个
update #temp set groupid=id/5 --5为每组的单位个数
--生成SQL语句处理字符串
--初始化
select @sql0= ' '
,@sql1= ' '
-- ...
-- ,@sqln
--得到处理字符串
select @sql0=@sql0+值 from #temp where groupid=0 --第一个变量
select @sql1=@sql1+值 from #temp where groupid=1 --第二个变量
--select @sqln=@sqln+值 from #temp where groupid=n --第n个变量
--查询
exec( 'select 日期=convert(varchar(10),日期,120) '
+@sql0+@sql1
-- ...+@sqln
+ ' from tb group by convert(varchar(10),日期,120)
')
--删除临时表
drop table #temp
/*
优点:比较灵活,数据量大时只需要增加变量就行了.不用改动其他部分
缺点:要自行估计处理的数据,估计不足就会出错
*/
--*/
--/*--方法2. bcp+isql
--因为要用到bcp+isql,所以需要这些信息
declare @servername varchar(250),@username varchar(250),@pwd varchar(250)
select @servername= 'zj ' --服务器名
,@username= ' ' --用户名
,@pwd= ' ' --密码
declare @tbname varchar(50),@sql varchar(8000)
--创建数据处理临时表
set @tbname= '[##temp_ '+convert(varchar(40),newid())+ '] '
set @sql= 'create table '+@tbname+ '(值 varchar(8000))
insert into '+@tbname+ ' values( ' 'create view '
+stuff(@tbname,2,2, ' ')+ ' as
select 日期=convert(varchar(10),日期,120) ' ') '
exec(@sql)
set @sql= 'insert into '+@tbname+ '
select ' ',[ ' '+单位名称+ ' ']=sum(case 单位名称 when ' ' ' ' ' '
+单位名称+ ' ' ' ' ' ' then 销售额 else 0 end) ' '
from(select distinct 单位名称 from tb) a '
exec(@sql)
set @sql= 'insert into '+@tbname+ '
values( ' 'from tb group by convert(varchar(10),日期,120) ' ') '
exec(@sql)
--生成创建视图的文件,注意使用了文件:c:/temp.txt
set @sql= 'bcp " '+@tbname+ ' " out "c:/temp.txt " /S " '
+@servername+ ' " /U " '+@username+ ' " /P " '+@pwd+ ' " /c '
exec master..xp_cmdshell @sql
--删除临时表
set @sql= 'drop table '+@tbname
exec(@sql)
--调用isql生成数据处理视图
set @tbname=stuff(@tbname,2,2, ' ')
set @sql= 'isql /S " '+@servername
+case @username when ' ' then ' " /E ' else ' " /U " '+@username+ ' " /P " '+@pwd+ ' " ' end
+ ' /d " '+db_name()+ ' " /i "c:/temp.txt " '
exec master..xp_cmdshell @sql
--调用视图,显示处理结果
set @sql= 'select * from '+@tbname+ '
drop view '+@tbname
exec(@sql)
/*
优点:程序自动处理,不存在判断错误的问题
缺点:复杂,经过的步骤多,容易出错,而且需要一定的操作员权限
*/
--*/
扫一扫
2023
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
