使用数据透视表填充数据

Question

我有一个像这样的表格：

country 2007    2008    2009
 UK      5       10      20
 uk      5       10      20
 us     10       30      40
 us     10       30      40

但我想要像这样填充表格：

country year    Total volumn
 uk     2007    10
 uk     2008    20
 uk     2009    40
 us     2007    20
 us     2008    60
 us     2009    80

如何使用Pivot表或其他方法在SQL Server 2008中完成此操作。

http://sqlfiddle.com/#!3/2499a

- user2567873

---

请提供更多细节，但在第一次检查时请注意Pivot() Over()。 - RoughPlace

Answer 1

SELECT country, [year], SUM([Total volumn]) AS [Total volumn]
FROM (
      SELECT country, [2007], [2008], [2009]
      FROM dbo.test137
      ) p
UNPIVOT 
([Total volumn] FOR [year] IN ([2007], [2008], [2009])
) AS unpvt
GROUP BY country, [year]
ORDER BY country, [year]

请在 SQLFiddle 上查看演示。

- Aleksandr Fedorenko

---

非常好。可能需要添加 ORDER BY country, year 以按照指定目标的顺序查看结果。 - Der U

Answer 2

尝试以下操作 - 记得使用您自己的数据库名称替换[DB]。在我的示例中，您不需要编写年份，但它们会自动为您提取。

-- will contain the temporary total
create table #tempResult (total int) 

-- get all countries
declare @countries table (country varchar(50))
insert into @countries
select country from table1

-- get all years
declare @years table(id int identity(1,1), [year] int)
insert into @years
SELECT column_name
FROM [DB].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Table1'
and column_name != 'country'

-- get all combinations
declare @result table(id int identity(1,1),country varchar(50),[year] int,total int)
insert into @result 
select distinct upper(c.country),y.[year],0
from @years as y,@countries as c

-- will be used for the looping
declare @counter int
select @counter = 1

while @counter <= (select count(*) from @result)
begin

declare @year int
declare @country varchar(50)

-- get year and country in question
select @year = [year] from @result where id = @counter
select @country = country from @result where id = @counter

declare @total int
select @total = (select sum(@year) from table1 where country = @country)

-- insert temp result 
declare @sql nvarchar(max)
set @sql = N'insert into #tempResult select sum([' + cast(@year as varchar(50)) + ']) from table1 where country = ''' + @country + ''''
print @sql

exec (@sql)

-- extract
select top 1 @total =  total from #tempResult

-- update respectively
update @result set total = @total
where country=@country and [year]=@year

-- clear
delete from #tempResult

select @counter = @counter + 1
end 

 -- select result
 select * From @result 

 drop table #tempResult

- Mez

Answer 3

如果您使用的是SQL Server 2008+，则可以使用CROSS APPLY将数据从列转换为行。

您可以使用CROSS APPLY和VALUES子句：

select distinct t.country,
  c.year,
  c.totalvolumn
from yourtable t
cross apply
(
  values 
  ('2007', 2007),
  ('2008', 2008),
  ('2009', 2009)
) c(year, TotalVolumn)
order by t.country, c.year;

请查看带有演示的SQL Fiddle

或者您可以使用 UNION ALL 和 CROSS APPLY：

select distinct t.country,
  c.year,
  c.totalvolumn
from yourtable t
cross apply
(
  select '2007', 2007 union all
  select '2008', 2008 union all
  select '2009', 2009 
) c(year, TotalVolumn)
order by t.country, c.year;

请参见带有演示的SQL Fiddle。

这也可以使用 UNION 查询来编写：

select country, '2007' year, 2007 totalVolumn
from yourtable 
union
select country, '2008' year, 2008 totalVolumn
from yourtable 
union
select country, '2009' year, 2009 totalVolumn
from yourtable 
order by country, year;

请查看带演示的SQL Fiddle

。

- Taryn