Oracle中如何把纵表转换为横表
纵表格式:
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)相关阅读:
检测用户浏览器是否支持CSS3的方法
FreeTextBox(版本3.1.6)在ASP.Net 2.0中使用方法
浅析Linux内核中的循环链表结构
img usemap属性 中国地图链接
如何让IIS与Apache共用80端口方法
nextSibling
XmlUtils JS操作XML工具类
在子窗体中预览报表
十天学会php之第八天
Oracle数据库的使用技巧五则
显示自己的IP的批处理bat文件
在JSTL EL中处理java.util.Map,及嵌套List的情况
PHP新手上路(四)
mysql同步复制搭建方法指南详细步骤