文档章节

SqlServer和Oracle中一些常用的sql语句3 行列转换

深圳大道
 深圳大道
发布于 2016/12/29 15:39
字数 1156
阅读 3
收藏 0

--217, SQL SERVER
SELECT Cust_Name
       , MAX(CASE WHEN Order_Date ='2009-08-01' THEN AR END) "2009-08-01"
       , MAX(CASE WHEN Order_Date ='2009-08-02' THEN AR END) "2009-08-02"
       , MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END) "2009-08-03"
FROM 
    (
     SELECT CONVERT(CHAR(10), Order_Date, 120) Order_Date
           , CUST_NAME
           , SUM(Qty * Price) AR
     FROM Orders
     WHERE Order_Date BETWEEN '2009-08-01' 
                                 AND CAST('2009-08-03' AS datetime) +1
     GROUP BY CONVERT(CHAR(10), Order_Date, 120)
               , CUST_NAME
     UNION ALL
     SELECT CONVERT(CHAR(10), Order_Date, 120) Order_Date
            ,NULL CUST_NAME
            , SUM(Qty * Price) AR
     FROM Orders
     WHERE Order_Date BETWEEN '2009-08-01' 
                         AND CAST('2009-08-03' AS datetime) +1
     GROUP BY CONVERT(CHAR(10), Order_Date, 120)
     ) A
GROUP BY Cust_Name


--218, SQL SERVER
SELECT Cust_Name
, SUM(
      CASE WHEN CONVERT(CHAR(10),Order_Date, 120)='2009-08-01' THEN Qty* Price END
      ) "2009-08-01"
, SUM(
      CASE WHEN CONVERT(CHAR(10),Order_Date, 120) ='2009-08-02' THEN Qty* Price  END
      ) "2009-08-02"
, SUM(
      CASE WHEN CONVERT(CHAR(10),Order_Date, 120) ='2009-08-03' THEN Qty* Price  END
      ) "2009-08-03"
FROM Orders 
WHERE 1=1
      AND Order_Date BETWEEN '2009-08-01' AND CAST('2009-08-03' AS datetime) +1
GROUP BY Cust_Name

--218, ORACLE
SELECT Cust_Name
      , MAX(CASE WHEN Order_Date ='2009-08-01' THEN AR END) "2009-08-01"
      , MAX(CASE WHEN Order_Date ='2009-08-02' THEN AR END) "2009-08-02"
      , MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END) "2009-08-03"
FROM 
    (
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD')Order_Date
           , CUST_NAME
         , SUM(Qty * Price) AR
    FROM Orders
    WHERE Order_Date BETWEEN DATE'2009-08-01' 
                       AND DATE'2009-08-03' +1
    GROUP BY TO_CHAR(Order_Date, 'YYYY-MM-DD')
              , CUST_NAME
    UNION ALL
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD') Order_Date
           ,NULL CUST_NAME
           , SUM(Qty * Price) AR
    FROM Orders
    WHERE Order_Date BETWEEN DATE'2009-08-01' 
                       AND TO_DATE('2009-08-03', 'YYYY-MM-DD') +1
    GROUP BY Order_Date
    ) A
GROUP BY Cust_Name 

--220, SQL SERVER
SELECT Cust_Name
       , "2009-08-01"
       , "2009-08-02"
       , "2009-08-03"
FROM 
  (
  SELECT CONVERT(CHAR(10),Order_Date, 120) Order_Date
        , Cust_Name
        , Qty * Price AR
  FROM Orders
  WHERE 1=1
        AND Order_Date BETWEEN '2009-08-01' AND '2009-08-04'
  )AS D
PIVOT
  (
  SUM(AR)
  FOR Order_Date 
        IN ([2009-08-01], [2009-08-02], "2009-08-03")
  ) AS P
--220, SQL SERVER
SELECT Order_Date
       ,[李先生] 
       , [张先生]
       , [曹先生]
       , [陈先生]
FROM 
  (
  SELECT CONVERT(CHAR(10),Order_Date, 120) Order_Date
           , Cust_Name
           , Qty
           , Price
           , Qty*Price AS AR
  FROM Orders
  WHERE 1=1
        AND Order_Date BETWEEN '2009-08-01' AND '2009-08-04'
  )AS D
PIVOT
  (
    SUM(AR)
           FOR Cust_Name 
               IN ([李先生], [张先生], [曹先生], [陈先生])
  ) AS P
  
--222, ORACLE
SELECT Cust_Name
       , "2009-08-01"
       , "2009-08-02"
       , "2009-08-03"
FROM 
  (
  SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD') Order_Date
         , Cust_Name
         --, Qty
         --, Price
         , Qty * Price AR
  FROM ORDERS
  WHERE 1=1
         AND Order_Date BETWEEN date'2009-08-01' AND date'2009-08-04'
) D
PIVOT
  (
  SUM(AR)
  FOR Order_Date
       IN ('2009-08-01' AS "2009-08-01", '200908-02' "2009-08-02", '2009-08-03' "2009-08-03")
  ) P

--223, ORACLE
SELECT *
FROM 
    (
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD') OrderDate
          , Cust_Name
          , Qty
          , Price
    FROM Orders
    WHERE 1=1
           AND Order_Date BETWEEN date'2009-08-01' AND date'2009-08-04'
    )
    PIVOT
    (   
       SUM(Qty * Price) AS "AR"
       , COUNT(*) AS "Qty" 
       FOR OrderDate
             IN ('2009-08-01' AS "08-01", '2009-08-02' "08-02", '2009-08-03' "08-03")
    ) P


 

--226, SQL SERVER
DROP TABLE Orders_Pivot
G0

SELECT Cust_Name
       , MAX(CASE WHEN Order_Date ='2009-08-01' THEN AR END) "2009-08-01"
       , MAX(CASE WHEN Order_Date ='2009-08-02' THEN AR END) "2009-08-02"
       , MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END) "2009-08-03"
       INTO Orders_Pivot
FROM 
    (
    SELECT CONVERT(CHAR(10), Order_Date, 120) Order_Date
           , CUST_NAME
           , SUM(Qty * Price) AR
    FROM Orders
    WHERE 1=1
           AND Order_Date BETWEEN '2009-08-01' AND CAST('2009-08-03' AS datetime) +1
    GROUP BY CONVERT(CHAR(10), Order_Date, 120)
             , CUST_NAME
    ) A
GROUP BY Cust_Name

--226, ORACLE
DROP TABLE Orders_Pivot;

CREATE TABLE Orders_Pivot
AS
SELECT Cust_Name
      , MAX(CASE WHEN Order_Date ='2009-08-01' THEN AR END) "2009-08-01"
      , MAX(CASE WHEN Order_Date ='2009-08-02' THEN AR END) "2009-08-02"
      , MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END) "2009-08-03"
FROM 
    (
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD')Order_Date
           , CUST_NAME
         , SUM(Qty * Price) AR
    FROM Orders
    WHERE Order_Date BETWEEN DATE'2009-08-01' 
                       AND DATE'2009-08-03' +1
    GROUP BY TO_CHAR(Order_Date, 'YYYY-MM-DD')
              , CUST_NAME
    ) A
GROUP BY Cust_Name 


--227, SQL SERVER
SELECT Order_Date, Cust_Name, AR
FROM 
     (
     SELECT CAST('2009-08-01' AS datetime) Order_Date -- SQL SERVER
            --DATE'2009-08-01' -- ORACLE
            , Cust_Name
            , "2009-08-01" AR
     FROM Orders_Pivot
     UNION ALL
     SELECT CAST('2009-08-02' AS datetime) Order_Date
            , Cust_Name
            , "2009-08-02" AR
     FROM Orders_Pivot
     UNION ALL
     SELECT CAST('2009-08-03' AS datetime) Order_Date
            , Cust_Name
            , "2009-08-03" AR
     FROM Orders_Pivot
     ) A
WHERE AR IS NOT NULL


--227, ORACLE
SELECT Order_Date, Cust_Name, AR
FROM 
     (
     SELECT DATE'2009-08-01' Order_Date
            , Cust_Name
            , "2009-08-01" AR
     FROM Orders_Pivot
     UNION ALL
     SELECT DATE'2009-08-02' Order_Date
            , Cust_Name
            , "2009-08-02" AR
     FROM Orders_Pivot
     UNION ALL
     SELECT DATE'2009-08-03' Order_Date
            , Cust_Name
            , "2009-08-03" AR
     FROM Orders_Pivot
     ) A
WHERE AR IS NOT NULL 

--228, SQL SERVER
SELECT Order_Date 
       , Cust_Name
       , AR
       , SUBSTRING(Order_Date, 6,5) "Date"
FROM 
  (
  SELECT Cust_Name, "2009-08-01" , "2009-08-02", "2009-08-03"  
  FROM Orders_Pivot
  ) D
UNPIVOT
  (
     AR FOR Order_Date 
          IN ([2009-08-01], [2009-08-02], "2009-08-03") 
  ) P

--228, ORACLE
SELECT Order_Date 
       , Cust_Name
       , AR
       , SUBSTR(Order_Date, 6,5) "Date"
FROM 
  (
  SELECT Cust_Name, "2009-08-01" , "2009-08-02", "2009-08-03"  
  FROM Orders_Pivot
  ) D
UNPIVOT
  (
     AR FOR Order_Date 
          IN ("2009-08-01", "2009-08-02", "2009-08-03") 
  ) P
  
--230, ORALCE / SQL SERVER 
SELECT P.Cust_Name
       , P."2009-08-01"
       , P."2009-08-02"
       , P."2009-08-03"
       , I.N
FROM Orders_Pivot P, Tally I
WHERE 1=1
       AND N<=3
ORDER BY 1, 4
 

--231, ORALCE / SQL SERVER
SELECT Order_Date
       , Cust_Name
       , AR
FROM 
    (
    SELECT 
         CASE N WHEN 1 THEN '2009-08-01'
                 WHEN 2 THEN '2009-08-02'
                 WHEN 3 THEN '2009-08-03'
         END Order_Date
         , Cust_Name
         , CASE N WHEN 1 THEN "2009-08-01"
                   WHEN 2 THEN "2009-08-02"
                   WHEN 3 THEN "2009-08-03"
          END AR
    FROM Orders_Pivot P, Tally I 
    WHERE 1=1
          AND N<=3
    ) A
WHERE AR IS NOT NULL


--234, ORACLE/SQL SERVER 
SELECT EmpName
      , Major
      , ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY Major) FldIdx
FROM Specialty

--236, ORACLE / SQL SERVER
SELECT EmpName
      , MAX(CASE WHEN FldIdx=1 THEN Major END) Fld1
      , MAX(CASE WHEN FldIdx=2 THEN Major END) Fld2
      , MAX(CASE WHEN FldIdx=3 THEN Major END) Fld3
      , COUNT(*) FldCount
FROM 
       (
      SELECT EmpName
              , Major
              , ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY Major) FldIdx
      FROM Specialty
      ) A
GROUP BY EmpName
ORDER BY COUNT(*)DESC



 

本文转载自:http://blog.csdn.net/smartsmile2012/article/details/9145233

深圳大道
粉丝 3
博文 877
码字总数 0
作品 0
深圳
架构师
私信 提问
数据库访问之Sqlite的不同之处

在我的Winform开发框架中,底层的数据访问支持多种不同的数据库,在数据库访问的开发过程中,发现Sqlite多数情况下,操作都和SqlServer或者说是和标准Sql差不多的。当然,Sqlite本身也有一些...

长平狐
2012/08/22
90
0
数据库访问之Sqlite的不同之处

在我的Winform开发框架中,底层的数据访问支持多种不同的数据库,在数据库访问的开发过程中,发现Sqlite多数情况下,操作都和SqlServer或者说是和标准Sql差不多的。当然,Sqlite本身也有一些...

长平狐
2012/06/11
73
0
OpenJWeb 1.8数据库管理工具功能说明

QQ:29803446 msn:baozhengw999@hotmail.com email:baozhengw@163.com 联系人:王先生 OpenJWeb 1.8数据库管理工具功能说明 由于使用sqlserver 的DTS跨数据库复制功能经常失败,所以openjweb开...

迷途d书童
2012/03/09
65
0
使用 Informatica 做将字符串转换成日期

数据源为 SQLServer 数据库,且源字段 varchar 类型。需要抽取到 Oracle 数据库中,并且转换为 date 类型。 由于源表和目标表是两种不同的数据库,且数据类型不同,所以无法在源表限定转换器...

DreamOver
2018/06/08
0
0
基于AEAI ESB的处理大字段

1.背景 数据迁移中大字段一直是个比较头疼的问题,一旦大字段中值大于32K的话就无法用sql语句来实现插入和更新。通用的解决办法一般有两种:存储过程和JDBC。下面主要总结下基于AEAI ESB用J...

数通畅联
2015/01/22
0
0

没有更多内容

加载失败,请刷新页面

加载更多

代码持续自动发布

需求: 自动更新私人gitbook项目代码 如果代码没更新,不做务必要的构建操作 思路 定时任务加shell脚本,无论更新没更新都执行git pull。 优点:能满足需求 资源浪费,代码仓库不更新也执行p...

阿dai学长
24分钟前
6
0
sparkstreaming原理

Spark Streaming是Spark生态系统当中一个重要的框架,它建立在Spark Core之上,下面这幅图也可以看出Sparking Streaming在Spark生态系统中地位。 Spark Streaming是Spark Core的扩展应用,它...

七旬少女
43分钟前
0
0
springmvc集成cas,并解决前后端分离情况

1.最近项目需要集成已经存在的cas系统。 但是目前已集成的系统都是jsp。而我们项目是前后端分离开发(伪),没有分开部署。 2.cas原理就不介绍了 网上例子很多。基本都是使用302重定向实现的...

起名字什么的太麻烦了
58分钟前
4
0
HDFS-原理

1. 写操作 客户端要向HDFS写数据,首先要跟Namenode通信以确认可以写文件并获得接收文件block的Datanode,然后,客户端按顺序将文件逐个block传递给相应Datanode,并由接收到block的Datanod...

叶枫啦啦
今天
3
0
聊聊elasticsearch的MembershipAction

序 本文主要研究一下elasticsearch的MembershipAction MembershipAction elasticsearch-6.7.1/server/src/main/java/org/elasticsearch/discovery/zen/MembershipAction.java public class M......

go4it
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部