如何在下面的场景中将列转换为行。 [英] How to transpose columns to rows in below scenario.
问题描述
我有一个场景,我需要将列转置为行。
从状态se中选择se.timestamp,e.description,se.created_by ,status_desc e其中e.id = se.id和se.shipment_id = 12550060按时间戳排序;
O / P:
时间戳状态所有者
12/12/2017 11:08一个系统
12/12/2017 11:08 B系统
12/12 / 2017 12:37 C system
12/12/2017 14:38 C system
12/12/2017 14:55 C system
12/12/2017 14:55 C系统
12/12/2017 17:31 D A2A
12/13/2017 11:34 E A2A
我需要显示这个(所有行显示为列):
时间戳1状态1所有者1时间戳2状态2所有者2时间戳3状态3所有者3时间戳4状态4所有者4时间戳5状态5所有者5时间戳6状态6所有者6时间戳7状态7所有者7时间戳8状态8所有者8
12 / 12/2017 11:08系统12/12/2017 11:08 B系统12/12/2017 12:37 C system 12/12/2017 14:38 C system 12/12/2017 14:55 C system 12/12/2017 14:55 C system 12/12/2017 17:31 D A2A 12/13/2017 11:34 E A2A
我尝试过:
我试过了:
max(当e.id ='1'然后e.description结束时的情况)但这个东西只显示状态'C'一次不是4次。
I have a scenario in which i need to transpose the columns to the rows.
select se.timestamp, e.description,se.created_by from status se, status_desc e where e.id=se.id and se.shipment_id=12550060 order by timestamp ;
O/P:
Timestamp Status Owner
12/12/2017 11:08 A system
12/12/2017 11:08 B system
12/12/2017 12:37 C system
12/12/2017 14:38 C system
12/12/2017 14:55 C system
12/12/2017 14:55 C system
12/12/2017 17:31 D A2A
12/13/2017 11:34 E A2A
I need to show this like (all rows to appear as columns):
Timestamp 1 Status 1 Owner 1 Timestamp 2 Status 2 Owner 2 Timestamp 3 Status 3 Owner 3 Timestamp 4 Status 4 Owner 4 Timestamp 5 Status 5 Owner 5 Timestamp 6 Status 6 Owner 6 Timestamp 7 Status 7 Owner 7 Timestamp 8 Status 8 Owner 8
12/12/2017 11:08 A system 12/12/2017 11:08 B system 12/12/2017 12:37 C system 12/12/2017 14:38 C system 12/12/2017 14:55 C system 12/12/2017 14:55 C system 12/12/2017 17:31 D A2A 12/13/2017 11:34 E A2A
What I have tried:
I have tried:
max(case when e.id = '1' then e.description end) but this thing only shows status 'C' once not 4 times.
推荐答案
从这里开始:如何将行转换为列并再次使用SQL(Aka PIVOT和UNPIVOT) Oracle All Things SQL博客 [ ^ ]
这篇关于如何在下面的场景中将列转换为行。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!