--创建纵表TABLE_A

 

     

      CREATE TABLE 
      TABLE_A (
     
     

          NAME VARCHAR2(20), 
     
     

          SUBJECT VARCHAR2(20), 
     
     

          SCORE NUMBER(22,0));
      

     

--向表TABLE_A中插入数据

 
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '语文', 60);
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '数学', 70);
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '英语', 80);
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('李四', '语文', 90);
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('李四', '数学', 100);

插入数据后TABLE_A如下图：

 

--创建纵表TABLE_B

 
 

     

      CREATE TABLE TABLE_B (
     
     

          NAME VARCHAR2(20), 
     
     

          CHINESE NUMBER(22,0), 
     
     

          MATH NUMBER(22,0), 
     
     

          ENGLISH NUMBER(22,0));
      

     
 


--向表TABLE_B中插入数据

 
INSERT INTO TABLE_B (NAME, CHINESE, MATH, ENGLISH) VALUES ('张三', 60, 70, 80);
INSERT INTO TABLE_B (NAME, CHINESE, MATH, ENGLISH) VALUES ('李四', 90, 100, 0);


 插入数据后
 TABLE_B如下图：


 

--纵表转横表 TABLE_A-->TABLE_B



--方法一：使用case ... when ... then ... else ... end  函数

 
select name,
        sum (case subject when '语文' then score else 0 end) as chinese,
        sum (case subject when '数学' then score else 0 end) as math,
        sum (case subject when '英语' then score else 0 end) as english
        from Table_A
        group by name



--方法二：使用pivot

 
select * from Table_A pivot (max(score)for subject in('语文','数学','英语'))


--横表转纵表 TABLE_B-->TABLE_A

--方法一：使用union all


 
select NAME,'语文' as SUBject,chinese as score from Table_B union all
select NAME,'数学' as SUBject,math as score from Table_B union all
select NAME,'英语' as SUBject,english as score from Table_B
order by name,subject desc

--方法二：使用unpivot


select name,subject,score from Table_B
unpivot
(score for subject in (chinese,math,english))

目录结构如下： 
 
行转列 
列转行 
 
[一]、行转列 
 
 
 
1.1、初始测试数据
 
 
 
表结构：TEST_TB_GRADE
 
 
 
 
 

  create table TEST_TB_GRADE   
 
 
 
(   
  ID        NUMBER(10) not null,   
  USER_NAME VARCHAR2(20 CHAR),   
  COURSE    VARCHAR2(20 CHAR),   
  SCORE     FLOAT  
)  
 
 
create table TEST_TB_GRADE
(
  ID        NUMBER(10) not null,
  USER_NAME VARCHAR2(20 CHAR),
  COURSE    VARCHAR2(20 CHAR),
  SCORE     FLOAT
)
 
 初始数据如下图：
 

                         
 
 
 
1.2、 如果需要实现如下的查询效果图：
 

                      
 
 
 
这就是最常见的行转列，主要原理是利用decode函数、聚集函数(sum)，结合group by分组实现的，具体的sql如下：
 
 
 

  select t.user_name,   
 
 
 
  sum(decode(t.course, '语文', score,null)) as CHINESE,   
  sum(decode(t.course, '数学', score,null)) as MATH,   
  sum(decode(t.course, '英语', score,null)) as ENGLISH   
from test_tb_grade t   
group by t.user_name   
order by t.user_name  
 
 
select t.user_name,
  sum(decode(t.course, '语文', score,null)) as CHINESE,
  sum(decode(t.course, '数学', score,null)) as MATH,
  sum(decode(t.course, '英语', score,null)) as ENGLISH
from test_tb_grade t
group by t.user_name
order by t.user_name
  
 
 
1.3、延伸
 
 
 
如果要实现对各门功课的不同分数段进行统计，效果图如下：
 

                  
 
 
 
具体的实现sql如下：
 
 
 

  select t2.SCORE_GP,   
 
 
 
  sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE,   
  sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH,   
  sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISH   
from (   
  select t.course,   
         case when t.score  <60 then '00-60'  
              when t.score >=60 and t.score <80  then '60-80'  
              when t.score >=80 then '80-100' end as SCORE_GP,   
         count(t.score) as COUNTNUM   
  FROM test_tb_grade t   
  group by t.course,    
        case when t.score  <60  then '00-60'  
              when t.score >=60 and t.score <80  then '60-80'  
              when t.score >=80 then '80-100' end  
  order by t.course ) t2   
group by t2.SCORE_GP   
order by t2.SCORE_GP  
 
 
select t2.SCORE_GP,
  sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE,
  sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH,
  sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISH
from (
  select t.course,
         case when t.score  <60 then '00-60'
              when t.score >=60 and t.score <80  then '60-80'
              when t.score >=80 then '80-100' end as SCORE_GP,
         count(t.score) as COUNTNUM
  FROM test_tb_grade t
  group by t.course, 
        case when t.score  <60  then '00-60'
              when t.score >=60 and t.score <80  then '60-80'
              when t.score >=80 then '80-100' end
  order by t.course ) t2
group by t2.SCORE_GP
order by t2.SCORE_GP
  
[二]、列转行 
 
 
 
1.1、初始测试数据
 
        表结构：TEST_TB_GRADE2
 
 
 

  create table TEST_TB_GRADE2   
 
 
 
(   
  ID         NUMBER(10) not null,   
  USER_NAME  VARCHAR2(20 CHAR),   
  CN_SCORE   FLOAT,   
  MATH_SCORE FLOAT,   
  EN_SCORE   FLOAT  
)  
 
 
create table TEST_TB_GRADE2
(
  ID         NUMBER(10) not null,
  USER_NAME  VARCHAR2(20 CHAR),
  CN_SCORE   FLOAT,
  MATH_SCORE FLOAT,
  EN_SCORE   FLOAT
)
  
        初始数据如下图：
 

         
 
 
 
1.2、 如果需要实现如下的查询效果图：
 

                        
 
 
 
这就是最常见的列转行，主要原理是利用SQL里面的union，具体的sql语句如下：
 
 
 

  select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2    
 
 
 
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2    
union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2    
order by user_name,COURSE   
 
 
select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2 
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2 
union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2 
order by user_name,COURSE 
 
 
 
 也可以利用【 insert all into ... select 】来实现，首先需要先建一个表TEST_TB_GRADE3：
 
 
 

  create table TEST_TB_GRADE3     
 
 
 
    (    
      USER_NAME VARCHAR2(20 CHAR),     
      COURSE    VARCHAR2(20 CHAR),     
      SCORE     FLOAT     
    )    
 
 
create table TEST_TB_GRADE3  
    ( 
      USER_NAME VARCHAR2(20 CHAR),  
      COURSE    VARCHAR2(20 CHAR),  
      SCORE     FLOAT  
    )  
 
 再执行下面的sql：
 
 
 
 
 

  insert all  
 
 
 
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE)   
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE)   
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE)   
select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;   
commit;  
 
 
insert all
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE)
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE)
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE)
select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;
commit;
 
 别忘记commit操作，然后再查询TEST_TB_GRADE3，发现表中的数据就是列转成行了。
 
 
 
 
 
本文连接：http://sjsky.iteye.com/blog/1152167

最近工作中有需求，要从Excel表中提取一些统计信息，因为需求变动可能比较频繁，所以用VBA来做了。调试数据操作部分颇费周折，特记录一下。
 
1.数据源文件:[D:\4月份统计表.xlsx]：
 

 
 
2.最终的统计表格式：
 

 
 
3.计算规则：
 

 
 
要求：
 
A. 外观等级A，且WLD在451.5-458之间，且LOP在82-200之间判定为OK，其他为NG。
 
B.将NG的按照表面等级→WLD→LOP的优先顺序，判定是哪一项引起的不良，更新到数据源的 [筛选结果] 中。
 
C.在统计表中按照入库日期分组，统计总数量、OK数量、NG数量，以及NG产品中外观等级、WLD、LOP各占多大比例。
 
4.代码：
 
 
Option Explicit

Public Sub DataInquire()
    Dim strCnn As String, sqlStr As String, subSqlStr As String, strSql(1 To 4) As String '判断条件 1=OK,2=外观等级NG,3=WLDNG,4=LOPNG
    Dim MainBook As Workbook
    Dim Arr() As Variant, rngA As Range, rngB As Range
    Dim cnn As Object, rs As Object
    Dim i As Long, itemStr(1 To 4) As String '判定标志 1=OK,2=外观等级,3=WLD,4=LOP
    Dim sourceFile As String
    
    Set cnn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set MainBook = ThisWorkbook
    sourceFile = "D:\4月份统计表.xlsx"
    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sourceFile + "';Extended Properties='Excel 12.0;HDR=YES,IME=1'"
        
    With ThisWorkbook.Worksheets(1)
        strSql(1) = " And [外观等级] ='A' And [WLD] >=451.5 And [WLD] <=458 And LOP>=82 And LOP<200"
        strSql(2) = " And [外观等级] <>'A'"
        strSql(3) = " And ([WLD] <451.5 or [WLD] >458)"
        strSql(4) = " And (LOP>=82 or LOP<200"
        itemStr(1) = "OK"
        itemStr(2) = "外观等级"
        itemStr(3) = "WLD"
        itemStr(4) = "LOP"
        Set rngA = .Range("B" & .Cells(6000, "B").End(xlUp).Row + 1).Resize(1, 12) '//找到写数据的位置
    End With
    
    cnn.Open strCnn
    If cnn.State = 1 Then
        For i = 4 To 1 Step -1 '//更新筛选结果
            sqlStr = "update [数据源$] set [筛选结果]= '" & itemStr(i) & "' where 1=1 " & strSql(i)
            cnn.Execute sqlStr
        Next i
        '//Excel/Access不支持case when then end结构，要用IIF来代替
'        subSqlStr = "(select [序号], case [筛选结果] when 'OK' then 1 end as [OK]," & _
'                " case [筛选结果] when '外观等级' then 1 end as [外观等级]," & _
'                " case [筛选结果] when 'WLD' then 1 end as [WLD]," & _
'                " case [筛选结果] when 'LOP' then 1 end as [LOP]," & _
'                " from [数据源$]) B "
'       ///
        subSqlStr = "(select [衬底编号], iif( [筛选结果] = 'OK',1,0 ) as [OK]," & _
                " iif( [筛选结果] = '外观等级',1,0 ) as [外观等级]," & _
                " iif( [筛选结果] = 'WLD',1,0 ) as [WLD]," & _
                " iif( [筛选结果] = 'LOP',1,0 ) as [LOP]," & _
                " from [数据源$]) B "
        
        sqlStr = "select A.[入库日期],count(A.[入库日期]) as 总数量,sum(B.[OK]) as OK,(count(A.[入库日期])-sum(B.[OK])) as NG,sum(B.[外观等级])/(count(A.[入库日期])-sum(B.[OK]))," & _
                " sum(B.[WLD])/(count(A.[入库日期])-sum(B.[OK])),sum(B.[LOP])/(count(A.[入库日期])-sum(B.[OK]))" & _
                "from [数据源$] A, " & subSqlStr & " where A.[序号]=B.[序号] " & _
                "group by A.[入库日期] order by A.[入库日期] asc"
        Set rs = cnn.Execute(sqlStr)
        If Not rs.EOF Then
            Arr = WorksheetFunction.Transpose(rs.GetRows())
            Set rngA = rngA.Resize(UBound(Arr, 1), 12)
            rngA = Arr
            rngA.Borders.LineStyle = xlContinuous
            rngA.Resize(, 1).Offset(, 4).Resize(, 8).NumberFormatLocal = "0.00%"
        End If
        Set rs = Nothing
    End If
    cnn.Close
End Sub
 
 

 
 说明： 
A.要将[筛选结果]做细项分析，就要用到行转列。而Excel/Access中没有case when then end 结构，更没有pivot可以用，幸好有IIF可用。
 
B.Transpose函数使用有些限制，要求结果集不超过1024行[未验证]，且结果集不包含null[已验证]。
 
C.Excel作为数据库，查询速度明显比Access数据库以及SqlServer、Oracle速度慢很多。5万多行数据，要查询大约20秒了。
 
D.Excel表作为数据库使用，select和update可正常使用，但insert和delete不能用。使用select into 可以创建新的Excel工作表。
 
 

一、行转列
 

 给出下面的数据:
 CREATE TABLE Sales (Year INT, Quarter INT, Results INT)
 
YEAR        QUARTER     RESULTS
----------- ----------- -----------
2004           1          20
2004           2          30
2004           3          15
2004           4          10
2005           1          18
2005           2          40
2005           3          12
2005           4          27
想要的到结果:
YEAR        Q1          Q2          Q3          Q4
----------- ----------- ----------- ----------- -----------
2004          20          30          15          10
2005          18          40          12          27
这个SQL就可解决这个问题:
SELECT Year,
       MAX(CASE WHEN Quarter = 1
           THEN Results END) AS Q1,
       MAX(CASE WHEN Quarter = 2
           THEN Results END) AS Q2,
       MAX(CASE WHEN Quarter = 3
           THEN Results END) AS Q3,
       MAX(CASE WHEN Quarter = 4
           THEN Results END) AS Q4
FROM Sales
GROUP BY Year
解释一下为什么要加max的原因，因为不加max的话结果会是这样：
YEAR        Q1          Q2          Q3          Q4
----------- ----------- ----------- ----------- -----------
2004          20           -           -           -
2004           -          30           -           -
2004           -           -          15           -
2004           -           -           -          10
2005          18           -           -           -
2005           -          40           -           -
2005           -           -          12           -
2005           -           -           -          27
 
 
 
二、列转行
 
给出下面数据
 
CREATE TABLE SalesAgg
(  year INTEGER,
   q1 INTEGER,
   q2 INTEGER,
   q3 INTEGER,
   q4 INTEGER );

YEAR        Q1          Q2          Q3          Q4
----------- ----------- ----------- ----------- -----------
2004          20          30          15          10
2005          18          40          12          27

想要的结果
YEAR        QUARTER     RESULTS
----------- ----------- -----------
2004           1          20
2004           2          30
2004           3          15
2004           4          10
2005           1          18
2005           2          40
2005           3          12
2005           4          27
 
这个SQL就可以实现：
 
SELECT S.Year, Q.Quarter, Q.Results
FROM SalesAgg AS S,
     TABLE (VALUES(1, S.q1),
                  (2, S.q2),
                  (3, S.q3),
                  (4, S.q4))
            AS Q(Quarter, Results);
 
 每个values中对应列的数据类型必须相同，值可以任意，如1，2，3，4都是整形
 
下面解释一下执行的过程：
 核心是用table函数创建了一个表，Q是创建的视图名，这个表是用values实现的多行表，values实现虚表的例子：
 
db2 => select * from (values (1,2),(2,3)) as t1(col1,col2)

COL1        COL2
----------- -----------
 1           2
 2           3

  2 条记录已选择。
 
db2 => select * from (values 1) as a

 1
 -----------
           1

 1 条记录已选择。
  
 
所不同的是这里跟from子句中的一个表产生了关系，取出了表中的一列作为数据.