怎么把上面的数据显示成下面的样子
------解决方案--------------------
加了个2013的，避免太过特殊
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-21 15:17:34
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([pp_year] int,[pp_month] int)
insert [huang]
select 2014,3 union all
select 2014,3 union all
select 2014,6 union all
select 2014,9 union all
select 2014,3 union all
select 2014,4 union all
select 2014,2 union all
select 2013,1
--------------生成数据--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename('pp_month')+'=max(case when [pp_year]='+quotename([pp_year],'''')+' and pp_month='+quotename(pp_month,'''')+' then [pp_month] else null end)'
from (SELECT DISTINCT * FROM [huang])huang group by pp_month,[pp_year]
exec('select [pp_year]'+@s+' from (SELECT DISTINCT * FROM [huang])huang group by [pp_year]')
----------------结果----------------------------
/*
pp_year pp_month pp_month pp_month pp_month pp_month pp_month
----------- ----------- ----------- ----------- ----------- ----------- -----------
2013 1 NULL NULL NULL NULL NULL
2014 NULL 2 3 4 6 9
*/
------解决方案--------------------
create table #t(pp_year varchar(4),pp_month varchar(2))
insert #t
select '2014','2' union all select '2014','3' union all select '2014','4' union all select '2014','6' union all
select '2014','9' union all select '2014','3' union all select '2014','4' union all select '2014','4'
declare @s varchar(max)
select @s=isnull(@s+',','')+'['+pp_month+']' from #t group by pp_month order by pp_month
set @s='select pp_year,'+@s+' from #t pivot(max(pp_month) for pp_month in ('+@s+')) a'
exec(@s)