• 博客园logo
  • 会员
  • 周边
  • 众包
  • 新闻
  • 博问
  • 闪存
  • 赞助商
  • Chat2DB
    • 搜索
      所有博客
    • 搜索
      当前博客
  • 写随笔 我的博客 短消息 简洁模式
    用户头像
    我的博客 我的园子 账号设置 会员中心 简洁模式 ... 退出登录
    注册 登录
gooliugle
博客园    首页    新随笔    联系   管理    订阅  订阅
纵表转换为横表

纵表格式:

pid                 dtag  ttag  dqf    pval 

1001-1-1001    100    1       0    10.10

横表格式:

pid    h1  h2  h3

Select --Pid,Substr(Pid, 8, 4) As Dev_Type,
 Substr(Pid, 0, 6) As Dev_Type,
 To_Char(To_Date('20000101', 'yyyymmdd') + Dtag, 'YYYY-MM-DD') || ' ' ||
  Trunc(Ttag / 60) || ':00:00' As Datetime,
 -- Dtag, Ttag,
 Sum(Case Substr(Pid, 8, 4)
              When '1001' Then
               Pval
              Else
               0
      End) As Cy1,
 
 Sum(Case Substr(Pid, 8, 4)
              When '1002' Then
               Pval
              Else
               0
      End) As Cy2,
 Sum(Case Substr(Pid, 8, 4)
              When '1003' Then
               Pval
              Else
               0
      End) As Cy3,
 Sum(Case Substr(Pid, 8, 4)
              When '1004' Then
               Pval
              Else
               0
      End) As Cy4,
 Sum(Case Substr(Pid, 8, 4)
              When '1005' Then
               Pval
              Else
               0
      End) As Cy5,
 Sum(Case Substr(Pid, 8, 4)
              When '1006' Then
               Pval
              Else
               0
      End) As Cy6,
 Sum(Case Substr(Pid, 8, 4)
              When '1007' Then
               Pval
              Else
               0
      End) As Cy7,
 Sum(Case Substr(Pid, 8, 4)
              When '1008' Then
               Pval
              Else
               0
      End) As Cy8,
 Sum(Case Substr(Pid, 8, 4)
              When '1009' Then
               Pval
              Else
               0
      End) As C2h2,
 Sum(Case Substr(Pid, 8, 4)
              When '1010' Then
               Pval
              Else
               0
      End) As C2h6,
 Sum(Case Substr(Pid, 8, 4)
              When '1011' Then
               Pval
              Else
               0
      End) As Cy11,
 Sum(Case Substr(Pid, 8, 4)
              When '1012' Then
               Pval
              Else
               0
      End) As Cy12,
 Sum(Case Substr(Pid, 8, 4)
              When '1013' Then
               Pval
              Else
               0
      End) As Cy13,
 Sum(Case Substr(Pid, 8, 4)
              When '1014' Then
               Pval
              Else
               0
      End) As Cy14,
 Sum(Case Substr(Pid, 8, 4)
              When '1015' Then
               Pval
              Else
               0
      End) As Cy15,
 Sum(Case Substr(Pid, 8, 4)
              When '1016' Then
               Pval
              Else
               0
      End) As Cy16,
 Sum(Case Substr(Pid, 8, 4)
              When '1017' Then
               Pval
              Else
               0
      End) As Cy17,
 Sum(Case Substr(Pid, 8, 4)
              When '1018' Then
               Pval
              Else
               0
      End) As Cy18,
 Sum(Case Substr(Pid, 8, 4)
              When '1019' Then
               Pval
              Else
               0
      End) As Cy19

From Analog_Data_200701
--Where Dtag = '2558' And Ttag = '240' And Substr(Pid, 0, 6) = '1001-4'
Group By Substr(Pid, 0, 6), Dtag, Ttag --, Pval
Order By Dtag, Ttag, Substr(Pid, 0, 6)

 

 

posted on 2010-06-12 10:10  gooliugle  阅读(357)  评论(0)    收藏  举报
刷新页面返回顶部
博客园  ©  2004-2025
浙公网安备 33010602011771号 浙ICP备2021040463号-3