問題描述

我有一個表格，由于某種原因，它有這樣的硬編碼值:

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)，但我如何拆分它以便我有一個數(shù)字句點和一個年份的單個字符(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模板網(wǎng)！