主页 / dba / 问题 / 275165
Accepted
tale852150
Asked: 2020-09-09 10:06:24 +0800 CST

Oracle unpivot 表数据

  • 772

给定PerformerTours带有列的表：

FirstName, LastName, Date_1, City_1, State_1, Date_2, City_2, State_2, Date_3, City_3, State_3, Date_4, City_4, State_4

和数据

Donna, Summers, 01/01/20, Atlanta, Georgia
James, Taylor, 02/04/20, Miami, Florida, 04/11/20, Seattle, Washington
Bob, Dylan, 01/11/20, Buffalo, New York, 09/24/20, Nashville, Tennessee, 10/22/20, Chicago, Illinois, 12/12/20, Denver, Colorado
Kenny, Rankin, 06/30/20, Las Vegas, Nevada, 07/04/20, Boston, Massachusetts, 08/18/20, Cincinnati, Ohio

如何仅使用 Oracle SQL 将数据还原为如下定义：

Donna, Summers, 01/01/20, Atlanta, Georgia
James, Taylor, 02/04/20, Miami, Florida
James, Taylor, 04/11/20, Seattle, Washington
Bob, Dylan, 01/11/20, Buffalo, New York 
Bob, Dylan, 09/24/20, Nashville, Tennessee
Bob, Dylan, 10/22/20, Chicago, Illinois
Bob, Dylan, 12/12/20, Denver, Colorado
Kenny, Rankin, 06/30/20, Las Vegas, Nevada
Kenny, Rankin, 07/04/20, Boston, Massachusetts
Kenny, Rankin, 08/18/20, Cincinnati, Ohio
oracle

2 个回答

  1. Select FirstName, LastName, Date_1 as date, City_1 as city, State_1 as state
  From performertours
Union all
Select FirstName, LastName, Date_2, City_2, State_2
  From performertours where date_2 is not null
Union all
Select FirstName, LastName, Date_3, City_3, State_3
  From performertours where date_3 is not null
Union all
Select FirstName, LastName, Date_4, City_4, State_4
  From performertours where date_4 is not null
    • 1
  2. Best Answer
    SELECT FirstName, LastName, Date, City, State FROM performertours
  UNPIVOT Exclude nulls (
    (date,city,state)
    FOR tour    IN (
      (Date_1, City_1, State_1) AS 'T1',
      (Date_2, City_2, State_2) AS 'T2', 
      (Date_3, City_3, State_3) AS 'T3',
      (Date_4, City_4, State_4) AS 'T4'
    ));
    • 1

相关问题