问题描述
我有一个表格,由于某种原因,它有这样的硬编码值:
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