在SQL(Oracle)中將行劃分為特定列

一直在嘗試一個場景，我想將回傳資料分散到幾列而不是冗長的行中

例如

COL1       |  COL2    |   COL3
-----------------------------
DATAHEAD   |  VALUE1  | VALUE9
DATAHEAD   |  VALUE2  | VALUE10 
DATAHEAD   |  VALUE3  | VALUE11
DATAHEAD   |  VALUE4  | VALUE12
DATAHEAD   |  VALUE5  | VALUE13
DATAHEAD   |  VALUE6  | VALUE14
DATAHEAD   |  VALUE7  | VALUE15
DATAHEAD   |  VALUE8  | VALUE16 

I want it to spread  into 6/n columns dynamically like 
this below

COL1       |  COL2     |   COL3   | COL4     | COL 5  |    COL6  |
--------------------------------------------------------------------
DATAHEAD   |  VALUE1   | VALUE2   |  VALUE3  | VALUE4  |  VALUE5  |
DATAHEAD   |  VALUE6   | VALUE7   | VALUE8   |  VALUE9 |  VALUE10 |
DATAHEAD   |  VALUE11  | VALUE12  |  VALUE13 | VALUE14 |  VALUE15 |
DATAHEAD   |  VALUE16  |  Null    | Null     |  Null   |  Null    |

只要它像上述格式一樣分散，資料的數量就無關緊要。我看到了一些關于 Pivot() 的相關文章，但我不知道如何在這種情況下應用它。非常感謝您的幫助，因為我在 sql 方面不是很好。

uj5u.com熱心網友回復：

首先，我將已排序的單元格基礎制作成資料透視表。

    select col1, col2 as col from table1
     union
    select col1, col3 as col from table1 
;

然后我提供了哪些行d和列m將是值的資訊。

  select
    col1,
    col,
    trunc((rank() over (order by col)-1)/5) as d,
    mod(rank() over (order by col)-1,5) as m
  from
  (
    select col1, col2 as col from table1
     union
    select col1, col3 as col from table1 
  )
;

最后，我根據檔案做了一個支點。

select * from
(
  select
    col1,
    col,
    trunc((rank() over (order by col)-1)/5) as d,
    mod(rank() over (order by col)-1,5) as m
  from
  (
    select col1, col2 as col from table1
     union
    select col1, col3 as col from table1 
  )
)
pivot
(
  min(col) for m in (0 as COL2,1 as COL3,2 as COL4,3 as COL5,4 as COL6)
)
order by d
;

輸出：

DDL：

CREATE TABLE Table1
    ("COL1" varchar2(8), "COL2" varchar2(7), "COL3" varchar2(7))
;

INSERT ALL 
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE01', 'VALUE09')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE02', 'VALUE10')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE03', 'VALUE11')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE04', 'VALUE12')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE05', 'VALUE13')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE06', 'VALUE14')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE07', 'VALUE15')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE08', 'VALUE16')
SELECT * FROM dual
;

編輯1：

Oracle 中的rank over函式用于按順序對行進行編號。

例如，rank() over (order by col)是一列，其中有從1開始的數字，如果按col排列，它將是1、2、3，...如果重新排列行，排名仍然會保留根據col 列。

因此，我之前的解決方案按字母順序排列值，而不管它們在源表中的位置如何。

如果您想按第一列排序并在最后以相同的順序對第二列進行排序，您可以使用這個內部選擇：

    select
      col1,
      rank() over (order by col2) as r,
      col2 as col
    from table1
    union
    select 
      col1,
      rank() over (order by col2)   (select count(*) from table1) as r,
      col3 as col
    from table1 
;

然后生成的選擇將如下所示：

select * from
(
  select
    col1,
    col,
    trunc((r-1)/5) as d,
    mod(r-1,5) as m
  from
  (
    select
      col1,
      rank() over (order by col2) as r,
      col2 as col
    from table1
    union
    select 
      col1,
      rank() over (order by col2)   (select count(*) from table1) as r,
      col3 as col
    from table1 
  )
)
pivot
(
  min(col) for m in (0 as COL2,1 as COL3,2 as COL4,3 as COL5,4 as COL6)
)
order by d
;

標籤：

上一篇：BeanPostProcessorpostProcessAfterInitialization是否只需要與代理一起使用？

下一篇：創建觸發器以將新值插入表中