如何取消透视表：
+------+-----+------+--------------------------------------------------------------+----+----+----+----+----+----+----+----+----+-----+-----+-----+
| YEAR | SEC | DEPT | TITLE | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 |
+------+-----+------+--------------------------------------------------------------+----+----+----+----+----+----+----+----+----+-----+-----+-----+
| 2002 | C | 30 | Other transport equipment | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2002 | C | 31 | Furniture manufacturing | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 2002 | C | 32 | Other manufacturing | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
| 2002 | D | 30 | Repair and installation of machinery and equipment | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
| 2002 | D | 31 | Electricity, gas, steam and air conditioning | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| 2002 | D | 32 | Water collection, treatment and supply | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 |
| 2002 | E | 30 | Waste collection, treatment and disposal; materials recovery | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 |
| 2002 | E | 31 | Remediation activities and other waste management services | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 |
| 2002 | E | 32 | Construction of buildings | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 9 |
+------+-----+------+--------------------------------------------------------------+----+----+----+----+----+----+----+----+----+-----+-----+-----+
对此：
+------+-------+-----+------+---------------------------+-------+
| YEAR | MONTH | SEC | DEPT | TITLE | COUNT |
+------+-------+-----+------+---------------------------+-------+
| 2002 | 1 | C | 30 | Other transport equipment | 1 |
| 2002 | 1 | C | 31 | Furniture manufacturing | 2 |
| 2002 | 1 | C | 32 | Other manufacturing | 3 |
| 2002 | 2 | C | 30 | Other transport equipment | 1 |
| 2002 | 2 | C | 31 | Furniture manufacturing | 2 |
| 2002 | 2 | C | 32 | Other manufacturing | 3 |
+------+-------+-----+------+---------------------------+-------+
到目前为止，我有：
select YEAR, SEC, DEPT,TITLE, MONTH
from mytable u
unpivot
(
MONTH for mj in ([M1]
,[M2]
,[M3]
,[M4]
,[M5]
,[M6]
,[M7]
,[M8]
,[M9]
,[M10]
,[M11]
,[M12] )
) u;
但这给了我几个月的时间。我需要带有月份号和该月份的计数值。值1-9可以是任何数字。
与它一起工作
SELECT YEAR, SEC, DEPT,TITLE, MONTH, TOTAL
FROM
(SELECT YEAR, SEC, DEPT,TITLE
,M1
,M2
,M3
,M4
,M5
,M6
,M7
,M8
,M9
,M10
,M11
,M12
FROM db.dbo.mytable pvt) p
UNPIVOT
(TOTAL FOR MONTH IN
( M1
,M2
,M3
,M4
,M5
,M6
,M7
,M8
,M9
,M10
,M11
,M12)) AS unpvt;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句