使用 UNPIVOT 将列转换为行

2021-09-10 00:00:00 tsql sql-server-2008 sql-server

我有一个表格，由于某种原因，它有这样的硬编码值:

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

相关文章