問題描述
我有一個表格，由于某種原因，它有這樣的硬編碼值:
I have a table that for some reason has hardcoded values like so:
Row ID QtyC1 QtyC2 QtyC3 QtyC4 QtyN1 QtyN2 QtyN3 QtyN4
100 10 5 8 9 11 12 5 6
101 9 11 12 5 6 10 4 9
該表有 35 列和大約 12k 條記錄(意味著大約 500k 個值)并且正在不斷添加和修改.
The table has 35 columns and around 12k records (meaning around 500k values) and is being added to and amended constantly.
我正在嘗試將其轉換為:
I am trying to transpose this in a view into:
Row ID Year Period Val
100 C 1 10
100 C 2 5
100 C 3 8
100 C 4 9
100 N 1 11
100 N 2 12
100 N 3 5
100 N 4 6
到目前為止，我已設法使用此查詢將其拆分為單個值:
So far I have managed to split it out into single values using this query:
SELECT Row ID, YP, Val
FROM (SELECT Row ID
, QtyC1 AS C1
, QtyC2 AS C2
, QtyC3 AS C3
, QtyC4 AS C4
, QtyN1 AS N1
, QtyN2 AS N2
, QtyN3 AS N3
, QtyN4 AS N4
FROM MyTable
) SUB
UNPIVOT (Val FOR YP IN (C1,C2,C3,C4,N1,N2,N3,N4)) AS PVT
這給了我一個單一的識別值(例如 C1)，但我如何拆分它以便我有一個數字句點和一個年份的單個字符(1和 C)?
This is getting me a single identifying value (eg C1) but how can I split it so I have a numeric period and a single character for the year (1 and C)?
我可以看到可能只是將字符串分成兩部分，但如果可能的話，我希望有一種更簡潔的方法.
I can see it might be possible just splitting up the string into two parts but I was hoping for a cleaner way if possible.
推薦答案
為什么這看起來不干凈?
Why would this seem unclean?
SELECT Row ID, left(YP, 1) as year, cast(right(yp, 1) as int) as period, Val
FROM (SELECT Row ID
, QtyC1 AS C1
, QtyC2 AS C2
, QtyC3 AS C3
, QtyC4 AS C4
, QtyN1 AS N1
, QtyN2 AS N2
, QtyN3 AS N3
, QtyN4 AS N4
FROM MyTable
) SUB
UNPIVOT (Val FOR YP IN (C1,C2,C3,C4,N1,N2,N3,N4)) AS PVT
這篇關于使用 UNPIVOT 將列轉換為行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！