这个语句好难写，谁帮看下怎么弄，新手

[yzf86211861]

日期 设备 设备温度
20171120 SQM711LM01 1
20171120 SQM711LM02 2
20171120 SQM711LM03 3

20171121 SQM711LM01 4
20171121 SQM711LM02 5
20171121 SQM711LM03 6


20171122 SQM711LM01 7
20171122 SQM711LM02 8

20171123 SQM711LM08 9
20171123 SQM711LM09 10


日期 设备1温度 设备2温度 设备3温度 设备8温度 设备9温度
20171120 1 2 3
20171121 4 5 6
20171122 7 8
20171123 9 10

知道 是行列转换，不会写。
有几种设备 就出来几列。

[Space1208]

select t.日期, sum(decode(t.设备, 'SQM711LM01', t.设备温度,null)) as 设备1温度, sum(decode(t.设备, 'SQM711LM02', t.设备温度,null)) as 设备2温度, sum(decode(t.设备, 'SQM711LM03', t.设备温度,null)) as 设备3温度 from table1 t group by t.日期 order by t.日期

[碧水幽幽泉]

SQL实现

--1.创建测试表
create table tmp as
select '20171120' dtime,  'SQM711LM01' dev, 1 temperature from dual union all
select '20171120' dtime,  'SQM711LM02' dev, 2 temperature from dual union all
select '20171120' dtime,  'SQM711LM03' dev, 3 temperature from dual union all
select '20171121' dtime,  'SQM711LM01' dev, 4 temperature from dual union all
select '20171121' dtime,  'SQM711LM02' dev, 5 temperature from dual union all
select '20171121' dtime,  'SQM711LM03' dev, 6 temperature from dual union all
select '20171122' dtime,  'SQM711LM01' dev, 7 temperature from dual union all
select '20171122' dtime,  'SQM711LM02' dev, 8 temperature from dual union all
select '20171123' dtime,  'SQM711LM08' dev, 9 temperature from dual union all
select '20171123' dtime,  'SQM711LM09' dev, 10 temperature from dual;

--2.SQL实现
select dtime,
            max(decode(dev,'SQM711LM01',temperature,null)) as "设备1温度",
            max(decode(dev,'SQM711LM02',temperature,null)) as "设备2温度",
            max(decode(dev,'SQM711LM03',temperature,null)) as "设备3温度",
            max(decode(dev,'SQM711LM08',temperature,null)) as "设备8温度",
            max(decode(dev,'SQM711LM09',temperature,null)) as "设备9温度"
  from tmp
group by dtime
 order by dtime
 

[碧水幽幽泉]

没问题的话，此楼结贴！

[yzf86211861]

引用 3 楼 qq646748739 的回复:

没问题的话，此楼结贴！

我就是不能确定 这 设备 是 有几个，SQM711LM01 像这种 设备 名字 我有的 都不知道 上面 我只是举个 例子，这种设备是 按 车间，来的，不能 这么 写死 有办法吗.

[yzf86211861]

自己顶，想要点 干货.

[yzf86211861]

自己顶 求帮助

[acen_chen]

用动态sql吧。。。

[yzf86211861]

引用 6 楼 acen_chen 的回复:

用动态sql吧。。。

我一个 新手也不会那玩意， 求指点.

[acen_chen]

拼接sql字符串呀，根据有哪些设备，就拼接成上面形式的sql，然后 execute一下

[yzf86211861]

引用 8 楼 acen_chen 的回复:

拼接sql字符串呀，根据有哪些设备，就拼接成上面形式的sql，然后 execute一下

真不会， 我都没用过 动态 SQL 。

[junes06]

declare v_var varchar2(500); v_sql varchar2(3000); begin execute immediate 'select wm_concat(distinct dev) from tmp' into v_var; v_var:=replace(v_var,',',''','''); v_var:=''''||v_var||''''; v_sql:='select * from (select * from tmp) pivot (sum(temperature) for dev in ( '||v_var||') ) ' ; dbms_output.put_line(v_sql); end; 上边的v_sql是拼接的动态语句，定义一个游标变量

[二月十六]

http://bbs.csdn.net/topics/392297163?page=1#post-402939490

[Hello World,]

 SELECT '20171120' dtime , 'SQM711LM01' dev , 1 temperature INTO #temp 
 UNION ALL SELECT '20171120' dtime , 'SQM711LM02' dev , 2 temperature 
 UNION ALL SELECT '20171120' dtime , 'SQM711LM03' dev , 3 temperature 
 UNION ALL SELECT '20171121' dtime , 'SQM711LM01' dev , 4 temperature 
 UNION ALL SELECT '20171121' dtime , 'SQM711LM02' dev , 5 temperature 
 UNION ALL SELECT '20171121' dtime , 'SQM711LM03' dev , 6 temperature 
 UNION ALL SELECT '20171122' dtime , 'SQM711LM01' dev , 7 temperature 
 UNION ALL SELECT '20171122' dtime , 'SQM711LM02' dev , 8 temperature 
 UNION ALL SELECT '20171123' dtime , 'SQM711LM08' dev , 9 temperature 
 UNION ALL SELECT '20171123' dtime , 'SQM711LM09' dev , 10 temperature;
        
DECLARE @devs VARCHAR(max)
SELECT @devs= STUFF((SELECT DISTINCT ','+ dev FROM #temp FOR XML PATH('')),1,1,'')

EXEC ('SELECT * FROM ( SELECT dtime , temperature , dev FROM #temp ) AS s PIVOT ( MAX(temperature) FOR dev IN ('+ @devs +' ) ) AS b')

[Hello World,]

[hp961218]

大哥解决没，我也遇到了这个问题，有没有好的解决方法

[小当家e]

说下我的思路。 1. 定义游标：select rowid,设备 into v_count from 表 group by 设备 游标赋值cur.row,cur.设备 2. 定义变量2 :v_sql: for i=1 to cur.row v_sql=v_sql+'decode(dev,cur.设备,temperature,null) as cur.设备'+',' 然后注意逗号，用select 把for 后的sql串起来，拼成一个sql，用execute 执行这个select语句