项目经验之:SQL数据库行列转换(精华)

  1 --===============由行列转换普通==================================
  2 --部门                姓名   工资
  3 ------------------- -------- -----
  4 --A                 JACK       20
  5 --B                 Tom        30
  6 --A                 JONE       80
  7 --B                 peter      30
  8 --
  9 --结果
 10 --
 11 --部门       姓名           工资
 12 ---------  -----------     ----
 13 --A         JACK,JONE      100
 14 --B         Tom,peter      60
 15 if object_id('t1') is not null
 16 drop table t1
 17 go
 18 create table t(部门 varchar(10),姓名 varchar(10),工资 int)
 19 insert t select 'A','JACK',20
 20 union all select 'B','Tom',30
 21 union all select 'A','Jone',80
 22 union all select 'B','Peter',30
 23 go
 24 if object_id('fun') is not null
 25 drop function fun
 26 go
 27 create function fun(@dp varchar(50))
 28 returns varchar(8000)
 29 as
 30 begin
 31 declare @sql varchar(5000)
 32 set @sql = ''
 33 select @sql = @sql + ',' + 姓名 from t1 where 部门 = @dp
 34 return stuff(@sql,1,1,'')  --去掉前面的,
 35 end
 36 
 37 go
 38 select distinct 部门,dbo.fun(部门),sum(工资) from t1 group by 部门
 39 go
 40 --====================行列转换合并========================
 41 --Name    Subject     Result
 42 --张三    语文        80
 43 --张三    数学        90
 44 --张三    物理        85
 45 --李四    语文        85
 46 --李四    数学        92
 47 --李四    物理        82
 48 --
 49 --想变成    
 50 --姓名   语文   数学   物理
 51 --张三   80     90     85
 52 --李四   85     92     82
 53 if object_id('t1') is not null
 54 drop table t1
 55 go
 56 create table t1(Name nvarchar(50),Subject nvarchar(50),Result int)
 57 insert t1 select 'a','a1',80
 58 union all select 'a','a2',90
 59 union all select 'a','a3',85
 60 union all select 'b','a1',85
 61 union all select 'b','a2',92
 62 union all select 'b','a3',82
 63 go
 64 select * from t1
 65 go
 66 --一条SQL语句得出
 67 select Name,
 68 语文=Sum(Case when Subject='a1' then Result end),
 69 数学=Sum(Case when Subject='a2' then Result end),
 70 物理=Sum(Case when Subject='a3' then Result end)
 71 from t1
 72 group by Name
 73 
 74 go
 75 --动态SQL1
 76 --如果不知道科目(即科目是动态的)
 77 DECLARE @S VARCHAR (1000)
 78 SET @S='select Name'
 79 select @S= @S+',SUM(CASE subject WHEN '''+subject+''' THEN Result  END) AS ['+subject+']' 
 80 FROM (SELECT distinct subject from t1) bb
 81 SET @S= @S+' from t1 group by Name'
 82 exec(@S)
 83 --select Name,SUM(CASE subject WHEN 'a1' THEN Result  END) AS [a1],SUM(CASE subject WHEN 'a2' THEN Result  END) AS [a2],SUM(CASE subject WHEN 'a3' THEN Result  END) AS [a3] from t1 group by Name
 84 
 85 --动态SQL2
 86 declare @sql varchar(8000)
 87 set @sql = 'select Name,'
 88 select @sql = @sql +quotename(Subject) + '= isnull(Sum(Case  when Subject='''+Subject+''' then Result end),0),'   --给出判断当某些字段下有Null时会给0
 89 from t1 group by Subject 
 90 select @sql = @sql + ' sum(Result) total from t1 group by Name'
 91 exec(@sql)
 92 --select Name,[a1]= isnull(Sum(Case  when Subject='a1' then Result end),0),[a2]= isnull(Sum(Case  when Subject='a2' then Result end),0),[a3]= isnull(Sum(Case  when Subject='a3' then Result end),0), sum(Result) total from t1 group by Name
 93 
 94 --======================做统计===========================================
 95 --   1日  2日   3日   4日   5日  6日  7日  8日  9日 10日//日期
 96 --    1     1    2      2    2    8    9     1    9  1  
 97 --    1     3    3      2    2    8    3     9    1  9  
 98 --    ..
 99 --我想统计：1-5日,6-10日的累加值得下表
 --1-5日 6-10日
 -- 8      28
 -- 11     30
 if object_id('T1') is not null
 drop table T1
 go
 create table T1([1日] int, [2日] int, [3日] int, [4日] int, [5日] int,  [6日] int, [7日] int, [8日] int, [9日] int, [10日] int)
 
 insert T1 select     1,     1,    2,      2,    2,    8,    9,     1,    9,  1  
 union all select     1,     3,    3,      2,    2,    8,    3,     9,    1,  9 
 go
 select * from T1
 go
 
 select [1日]+  [2日]+   [3日]  + [4日]+   [5日] as [1-5日] from T1
 --=======================字段的累加================
 --内容是'82,65,32,98',也就是说,这个字段里面的数字是用","隔开的
 --,现在我想把这里面的数字取出来,并累加,想要得到它的累加值结果为"277"
 go
 if object_id('T1') is not null
 drop table T1
 go
 CREATE TABLE T1(id INT,string VARCHAR(100))
 INSERT T1
 SELECT 1,'82,65,32,98' UNION ALL
 SELECT 2,'10,20,30,40' 
 go
 select * from T1
 go
 --建临时表的语法---
 if object_id('A') IS NOT NULL 
 drop table A
 go
 CREATE TABLE A(id INT,string VARCHAR(100))
 INSERT A
 SELECT 1,'82,65,32,98' UNION ALL
 SELECT 2,'10,20,30,40' 
 
 SELECT * FROM A
 --建立辅助临时表
 --=================================================
 if object_id('#1') is not null
 drop table #1
 go
 SELECT TOP 8000 id = identity(int,1,1) 
 INTO #1 FROM syscolumns a, syscolumns b   
 
 --执行查询
 SELECT A.ID, Num = CAST(SUBSTRING(A.string, B.ID, CHARINDEX(',', A.string + ',', B.ID) - B.ID)  AS INT)
 INTO #2
 FROM A, #1 B
 WHERE SUBSTRING(',' + a.string, B.id, 1) = ','   
 GO
 if object_id('#2') is not null
 drop table #2
 SELECT ID,Total=SUM(CAST(Num AS INT))
 FROM #2
 GROUP BY ID
 --=========================================================
 go
 drop table A
 go
 DECLARE @str varchar(1000)
 SET @str = '82,65,32,98'
 
 DECLARE @re int
 DECLARE @s nvarchar(4000)
 SET @s = 'SET @re=' + REPLACE(@str, ',', '+')
 EXEC sp_executesql @s, N'@re int OUT', @re OUT
 SELECT @re
 
 --======================================
 
 go
 --=======================按月做统计报表================================
 --有表如下:
 --id  workdate ondutyName
 --1   2006-1-1  a;b;
 --2   2006-1-2  b;c;
 --3   2006-1-3  a;c;
 --.
 --workdate表示值班日期,ondutyName表示值班人员,以';'隔开不同的人员,
 --现在要得到这样的统计结果:
 --比如统计2006年1月每天的值班情况,得到以下的数据:
 --Name 1 2 3  .31
 --a    1 0 1 .
 --b    1 1 0 .
 --c    0 1 1 .
 --如果a这天值班了,就用1表示,不值班,就用0表示
 --如果统计的是2006年2月数据,因为2月只有28天,那么,生成的统计表的格式就如下:
 --Name 1 2 3 28
 --a    .
 --b    .
 --c    .
 --请问该怎样写这样的存储过程?
 go
 if object_id('t1') is not null
 drop table t1
 go
 create table t1(id int identity(1,1),workdate datetime,ondutyName varchar(50))
 insert t1 select '2006-1-1','a;b;'
 union all select '2006-1-2','b;c;'
 union all select '2006-1-3','c;d;'
 union all select '2006-1-4','d;e;'
 union all select '2006-1-5','a;c;'
 union all select '2006-1-6','g;b;'
 union all select '2006-1-7','e;c;'
 union all select '2006-1-8','b;d;'
 go 
 select * from t1
 go
 --用动态SQL
 select top 3000 identity(int,1,1)[id] into # from sysobjects a, sysobjects b
 go
 select substring(ondutyname+';',b.id,CHARINDEX(';',ondutyname+';',b.id)-b.id)Name,
 day(workdate)workdate
 into #t
 from (select * from t1 where workdate BETWEEN '2006-1-1' and '2006-1-31') a,# b 
 where substring(';'+ondutyname,b.id,1)=';'
 go
 
 declare @table varchar(1000)
 set @table='select Name,'
 select @table=@table+'(case when((select workdate from #t b where b.name=a.name and b.workdate='+
 QUOTENAME(workdate,'''')+'))is null then 0 else 1 end) as '+QUOTENAME(workdate)+','
 from #t
 group by workdate
 set @table = left(@table,len(@table)-1)+' from #t a'
 exec (@table)
 go
 ---=================================
 --
 --Year Quarter Amount
 --2000   1        1.1
 --2000   2        1.2
 --2000   3        1.3
 --2000   4        1.4
 --2001   1        2.1
 --2001   2        2.2
 --2001   3        2.3
 --2001   4        2.4
 --其中每行表表示一个季度的数据。
 --
 --如果处理表A中的数据，得到如下的结果。
 --Year Quarter1 Quarter2 Quarter3 Quarter4
 --2000   1.1      1.2      1.3     1.4
 --2001   2.1      2.2      2.3     2.4
 --请用SQL写一段代码实现。
 go
 if object_id('T1') is not null
 drop table T1
 go
 create table T1
 (
 year int,
 Quarter varchar(30),
 amount float
 )
 go
 insert T1 select 2000,'1',1.1
 insert T1 select 2000,'2',1.2
 insert T1 select 2000,'3',1.3
 insert T1 select 2000,'4',1.4
 insert T1 select 2001,'1',2.1
 insert T1 select 2001,'2',2.2
 insert T1 select 2001,'3',2.3
 insert T1 select 2001,'4',2.4
 insert T1 select 2001,'5',2.5
 go
 select * from T1
 delete from  t1 where Quarter='5'
 go
 --SQL得出结果
 select [Year],
 Quarter1=Sum(Case when Quarter = '1' then Amount end),
 Quarter2=Sum(Case when Quarter = '2' then Amount end),
 Quarter3=Sum(Case when Quarter = '3' then Amount end),
 Quarter4=Sum(Case when Quarter = '4' then Amount end)
 from T1
 group by [Year]
 --动态SQL
 --但数据当不是固定的
 declare @sql varchar(5000)
 set @sql = 'select [Year],'
 select @sql = @sql + 'Sum(Case when  Quarter = '''+T.Quarter+''' then Amount else 0 end) as Quarter' + T.Quarter +',' 
 from  (select Quarter from  T1 group by Quarter) T
 set @sql =left (@sql,len(@sql)-1) + ' from T1 group by Year'
 print @sql
 go
 --select [Year],Sum(Case when  Quarter = '1' then Amount else 0 end) as Quarter1,Sum(Case when  Quarter = '2' then Amount else 0 end) as Quarter2,Sum(Case when  Quarter = '3' then Amount else 0 end) as Quarter3,Sum(Case when  Quarter = '4' then Amount else 0 end) as Quarter4,Sum(Case when  Quarter = '5' then Amount else 0 end) as Quarter5 from T1 group by Year
 --============================================实用的SQL
 if object_id('bb') is not null
 drop table bb
 go
 CREATE TABLE [bb] (
  [prsa] [int] NULL ,
  [prend] [int] NULL ,
  [type] [char] (2) NULL 
 ) ON [PRIMARY]
 go
 insert bb select 12,213,'D'
 union all select 214,300,'D+'
 union all select 301,413,'C'
 union all Select 414,500,'C+'
 union all select 501,613,'B'
 union all select 614,700,'B+'
 union all select 701,800,'A'
 go
 select * from bb
 GO
 if object_id('aa') is not null
 drop table aa
 go
 CREATE TABLE [aa] (
  [name] [nvarchar] (20) NULL ,
  [pr] [int] NULL 
 ) ON [PRIMARY]
 GO
 insert aa select N'小1',521
 union all select N'王2',321
 union all select N'李3',152
 union all select N'李4',132
 go
 select * from aa
 --===========评出aa表中学生的等级(aa的表数据不固定)============================
 select aa.name,bb.type from aa inner join bb on aa.pr between bb.prsa and bb.prend
 
 --===========评出bb表中各等级学生数(即打A的有多少学生,打B的有多少学生)============================
 
 --第一种方法
 SELECT type,isnull(sum(cn),0) FROM bb b 
 left join (select 1 as cn,sum(pr)as prr from aa group by name) a
 on (a.prr BETWEEN b.prsa and b.prend)
 group by type
 --第二种方法
 select bb.type, count(pr) 小计 from bb left join aa on aa.pr between bb.prsa and bb.prend group by bb.type
 --=====================评出bb表中各等级学生有哪些===================
 if object_id('fun') is not null
 drop function fun
 go
 create function fun(@prsa int, @prend int)
 returns varchar(1000)
 as
 begin
  declare @re varchar(1000)
  set @re=''
  select @re=@re+','+name from aa where pr between @prsa and @prend
 
  return(stuff(@re, 1, 1, ''))
 end
 go
 
 select 
 bb.type,
 [count]=count(pr),
 [name]=dbo.fun(prsa, prend)
 from bb
 left join aa on aa.pr between bb.prsa and bb.prend
 group by bb.type, bb.prsa, bb.prend
 
 select name,等级=max(case when pr between prsa and prend then type else null end) from aa a, bb b
 group by name
 --2:
 select 等级=type,个数=sum(case when pr between prsa and prend then 1 else 0 end)  from bb b,aa a
 group by type 
 --3:
 select * from
 (
 select 等级=type,个数=sum(case when pr between prsa and prend then 1 else 0 end)  from bb b,aa a
 group by type 
 ) ta left join
 (
 select name,等级=max(case when pr between prsa and prend then type else null end) from aa a, bb b
 group by name
 ) tb
 on ta.等级=tb.等级
 
  
 
 --===========评出aa表中学生的等级(aa的表数据不固定)============================
 select aa.*, bb.type from aa
 join bb on aa.pr between bb.prsa and bb.prend
 
 --===========评出bb表中各等级学生数(即打A的有多少学生,打B的有多少学生)============================
 select type,isnull(sum(cn),0)[count] from bb
 left join (select 1 cn,* from aa)aa on aa.pr between bb.prsa and bb.prend
 group by type
 
 --=====================评出bb表中各等级学生有哪些===================
 select bb.type,aa.* from bb
 left join aa on aa.pr between bb.prsa and bb.prend
 
   
  
 --======================汇总SQL
 --表1  
 --入库时间      品名     数量    人员代码     单位    状态  
 --2007-01-01    AAAA     100       111        aaa      0  
 --2007-01-01    AAAA     20        111        bbb      0  
 --2007-01-01    BBBB     100       111        aaa      0  
 --2007-01-01    AAAA     100       222        aaa      1  
 -- 
 --表2  
 --入库时间      品名     数量    人员代码     单位    状态  
 --2007-01-01    AAAA     100       111        aaa      0  
 --2007-01-01    AAAA     20        111        bbb      0  
 --2007-01-01    BBBB     100       111        aaa      0  
 --2007-01-01    AAAA     100       222        aaa      1  
 -- 
 --表3  
 --人员代码          姓名  
 --111               张三  
 --222               李四  
 -- 
 --汇总表1与表2中品名为AAAA，单位为aaa，状态是0的数量，并且显示姓名。
 
 if object_id('A') is not null
 drop table A
 go
 create table A(入库时间 datetime, 品名 varchar(10), 数量 int, 人员代码 int, 单位 varchar(10), 状态 bit)
 insert A select '2007-01-01',  'AAAA',      100,         111,        'aaa',      0
 union all select '2007-01-01',  'AAAA',      20,          111,        'bbb',      0
 union all select '2007-01-01',  'BBBB',      100,         111,        'aaa',      0
 union all select '2007-01-01',  'AAAA',      100,         222,        'aaa',      1
 go
 if object_id('B') is not null
 drop table B
 go
 create table B(入库时间 datetime, 品名 varchar(10), 数量 int, 人员代码 int, 单位 varchar(10), 状态 bit)
 insert B select '2007-01-01',  'AAAA',      100,         111,        'aaa',      0
 union all select '2007-01-01',  'AAAA',      20,          111,        'bbb',      0
 union all select '2007-01-01',  'BBBB',      100,         111,        'aaa',      0
 union all select '2007-01-01',  'AAAA',      100,         222,        'aaa',      1
 
 create table C(人员代码 int, 姓名 varchar(10))
 insert C select 111,          '张三'
 union all select 222,          '李四'
 
 select 'AAAA'品名,'aaa'单位,(select 姓名 from c where c.人员代码=a.人员代码),sum(数量) 
 from(select * from a where 品名='AAAA' and 单位='aaa' and 状态='0'
 union all select * from a where 品名='AAAA' and 单位='aaa' and 状态='0')a
 group by 人员代码
 
 go
 drop table A
 drop table B
 go
 ----id     name         count     time
 --1       a            12     2007-2-7
 --2       b            13     2007-2-7
 --3       c             4     2007-2-7
 --4       d            15     2007-2-7
 --5       e             7     2007-2-7
 --6       a            21     2007-2-8
 --7       b            33     2007-2-8
 --8       c            11     2007-2-8
 --9       d            65     2007-2-8
 --10      e            10     2007-2-8
 --
 --期望结果：
 --    2007-2-7      2007-2-8
 --a      12             21
 --b      13             33
 --c       4             11
 --d      15             65
 --e       7             10
 if object_id('t') is not null
 drop table t
 go
 create table t(id int,[name] varchar(50),ount int,time datetime)
 insert t select 1,       'a',            '12',     '2007-2-7'
 union all select 2,       'b',          '13',     '2007-2-7'
 union all select 3,       'c',            '4',     '2007-2-7'
 union all select 4,       'd',           '15',     '2007-2-7'
 union all select 5,       'e',            '7',     '2007-2-7'
 union all select 6,       'a',           '21',     '2007-2-8'
 union all select 7,       'b' ,           '33',     '2007-2-8'
 union all select 8,       'c',            '11',     '2007-2-8'
 union all select 9,      'd',           '65',     '2007-2-8'
 union all select 10,      'e',            '10',     '2007-2-8'
 go
 select * from t
 go
 declare @sql varchar(8000)
 set @sql = 'select name,'
 select @sql = @sql + quotename(convert(varchar(50),t1.time,110)) + '=sum(case  when time = '''+convert(varchar(50),t1.time,110)+''' then ount end),'
 from (select time from t group by time) t1
 set @sql = left(@sql,len(@sql)-1) + ' from t group by name'
 exec @sql
 
 go
 declare @sql varchar(8000)
 set @sql = 'select name'
 select @sql = @sql + ',sum(case  when time = '''+convert(varchar(50),t1.time,110)+''' then ount end) ['+convert(varchar(10),[time],120)+']'
 from (select distinct time from t group by time) t1
 set @sql = @sql + 'from t group by name'
 print @sql
 go
 
 declare @sql varchar(8000)
 set @sql='select name'
 select @sql=@sql+',sum(case time when '''+convert(varchar(10),[time],120)+''' then [ount] end) ['+convert(varchar(10),[time],120)+']' from (select distinct [time] from  t) a
 select @sql=@sql+' from t group by name'
 print @sql
 --exec(@sql)