多行值合并为一行 [英] Multiple rows values into a single row

问题描述

我在 oracle SQL 中有一个要求，将多行转换为单行.

I have a requirement in oracle SQL where the multiple rows are to be converted into single row.

示例如下:

Empid Ele_name Inp_name Inp_Value   EntryId     Start_date      End Date
123     LW      MA      1637    100000104856397     06-Nov-17       31-Dec-12
123     LW      CA      Y       100000104856397     06-Nov-17       31-Dec-12
143     LW      MA      1637    100000104856504     06-Nov-17       31-Dec-12
143     LW      CA      Y       100000104856504     06-Nov-17       31-Dec-12
144     MTS     AA      1000    300002685277016     12-Dec-17       31-Dec-12
144     MTS     CA      Y       300002685277016     12-Dec-17       31-Dec-12
144     LW      CA      Y       300002685277012     06-Nov-17       31-Dec-12
144     LW      AA      200     300002685277012     06-Nov-17       31-Dec-12

预期的输出是

Empid   Element_name    FIrstName  LastName  Initials  Input_name1  Input_Value1    Input_name2  Input_Value2  Input_name3    Input_Value3     Input_name4    Input_Value4    Input_name5      Input_Value5       EntryId                Start_date     End Date
123       LW             null       null       null      MA            1637            CA              Y           null          null             null           null            null            null           100000104856397        06-Nov-17    31-Dec-12
143       LW             null       null       null      MA            1637            CA              Y           null          null             null           null            null            null           100000104856504        06-Nov-17     31-Dec-12
144       MTS             null       null       null      AA            1000            CA              Y           null          null             null           null            null            null           300002685277016        12-Dec-17    31-Dec-12
144       LW             null       null       null      CA            Y                AA            200           null          null             null           null            null            null           300002685277012        06-Nov-17    31-Dec-12

我听说这可以通过 Pivot 完成.但我对这个概念很陌生.任何人都可以在这里提供帮助.

I heard that this can be done with Pivot. But i am new to the concept. Can anyone help here.

推荐答案

你需要做更多的工作才能像这样旋转，因为旋转需要行数据并将其转换为列名，但没有任何行数据是1, 2, 3, 4... 用作列名(inp_value1 <-- 这里的 1)

You need to do some more work before you can pivot like that, because pivoting takes row data and makes it into column names, but none of your row data is 1, 2, 3, 4... to use as a column name (inp_value1 <-- the 1 here)

你可以这样做，这可能更容易理解:

You can do this, which is probably easier to understand:

SELECT
  Empid,
  Ele_name,
  MAX(CASE WHEN rown = 1 THEN Inp_name END) as Inp_name1,
  MAX(CASE WHEN rown = 1 THEN Inp_value END) as Inp_Value1,
  MAX(CASE WHEN rown = 2 THEN Inp_name END) as Inp_name2,
  MAX(CASE WHEN rown = 2 THEN Inp_value END) as Inp_Value2,
  MAX(CASE WHEN rown = 3 THEN Inp_name END) as Inp_name3,
  MAX(CASE WHEN rown = 3 THEN Inp_value END) as Inp_Value3,
  MAX(CASE WHEN rown = 4 THEN Inp_name END) as Inp_name4,
  MAX(CASE WHEN rown = 4 THEN Inp_value END) as Inp_Value4,
  MAX(CASE WHEN rown = 5 THEN Inp_name END) as Inp_name5,
  MAX(CASE WHEN rown = 5 THEN Inp_value END) as Inp_Value5,
  MAX(CASE WHEN rown = 6 THEN Inp_name END) as Inp_name6,
  MAX(CASE WHEN rown = 6 THEN Inp_value END) as Inp_Value6,
  EntryId,     
  Start_date,      
  End_Date
FROM
  (SELECT t2.*, ROW_NUMBER() OVER(PARTITION BY EmpId, Ele_name ORDER BY 1) as rown FROM t2) d
GROUP BY 
  Empid,
  Ele_name, 
  EntryId,     
  Start_date,      
  End_Date

• ps;如果它们都是，为什么在您的预期输出中指定 name3/4/5/6空值?如果每个 empid/ele_name 对的数据永远不会超过 2 行，那么您可以将 null 写为 input_name3.. 等等
• pps:我称我的表为 t2 - 在查询中编辑您的姓名
• pps;我不知道结束日期"一栏的名称是否真的有空格，我用下划线称呼我的

或者你可以像这样旋转(更难理解但更紧凑):

Or you can pivot like this (harder to understand but more compact):

SELECT
  Empid,
  Ele_name,
  pvt.*,
  EntryId,     
  Start_date,      
  End_Date
FROM
  (SELECT t2.*, ROW_NUMBER() OVER(PARTITION BY EmpId, Ele_name ORDER BY 1) as rown
   FROM t2) d
PIVOT( 
  MAX(inp_name) as inp_name, 
  MAX(inp_value) as inp_value 
  FOR rown in (1,2,3,4,5,6) 
) pvt

但是列将从 pvt.* 中出来，名称为 1_inp_name, 1_inp_value .. 你必须使用 AS 来重命名它们

but the columns will come out of the pvt.* with names as 1_inp_name, 1_inp_value .. You'll have to use AS to rename them

这篇关于多行值合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！