我有一个表格,由于某种原因,它有这样的硬编码值:
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 将列转换为行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!