我有一个表格,格式如下
Country_Code | 1960 | 1961 | 1962 | ..... | 2011 ------------------------------------------------ IND | va11 | va12 | va13 | ..... | va1x AUS | va21 | va22 | va23 | ..... | va2x ENG | va31 | va32 | va33 | ..... | va3x
我想将其转换为以下格式
Country_Code | Year | Value --------------------------- IND | 1960 | va11 IND | 1961 | va12 IND | 1962 | va13 . . IND | 2011 | va1x AUS | 1960 | va21 AUS | 1961 | va22 AUS | 1962 | va23 . . AUS | 2011 | va2x ENG | 1960 | va31 ENG | 1961 | va32 ENG | 1962 | va33 . . ENG | 2011 | va3x
解决方法
您可以使用
UNPIVOT完成.如果您有已知数量的列,那么您可以对值进行硬编码：
select Country_Code,year,value from yourtable unpivot ( value for year in ([1960],[1961],[1962],[2011]) ) u
DECLARE @colsUnpivot AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name != 'Country_Code'
for xml path('')),1,'')
set @query
= 'select country_code,value
from yourtable
unpivot
(
value
for year in ('+ @colsunpivot +')
) u'
exec(@query)
你甚至可以使用UNION ALL：
select country_code,'1960' year,1960 value from yourtable union all select country_code,'1961' year,1961 value from yourtable union all select country_code,'1962' year,1962 value from yourtable union all select country_code,'2011' year,2011 value from yourtable
