IF OBJECT_ID(N'Test') IS NOT NULL
DROP TABLE Test
GOCREATE TABLE Test(id INT,name VARCHAR(10),date VARCHAR(10), VALUE INT)
INSERT INTO Test
SELECT
1 ,'张三' ,'201301' ,11233 UNION ALL SELECT
1 ,'张三' ,'201302' ,22331 UNION ALL SELECT
1 ,'张三' ,'201303' ,442 UNION ALL SELECT
1 ,'张三' ,'201304' ,234 UNION ALL SELECT
2 ,'李四' ,'201301' ,11233 UNION ALL SELECT
2 ,'李四' ,'201302' ,22331 UNION ALL SELECT
2 ,'李四' ,'201303' ,442 UNION ALL SELECT
2 ,'李四' ,'201304' ,234 UNION ALL SELECT
3 ,'王五' ,'201301' ,11233 UNION ALL SELECT
3 ,'王五' ,'201302' ,22331 UNION ALL SELECT
3 ,'王五' ,'201303' ,442 UNION ALL SELECT
3 ,'王五' ,'201304' ,234 UNION ALL SELECT
3 ,'王五' ,'201305' ,22114
--------------------------------------------------------------查询---------------------------------------------------
DECLARE @Sql VARCHAR(8000)
SET @Sql='Select id,name '
SELECT @Sql=@Sql+' , isnull(convert(varchar(20),(Max(case [date] when '+date+' then VALUE end))),'''') '''+CASE WHEN LEFT(RIGHT(date,2),1)=0 THEN RIGHT(date,1) ELSE RIGHT(date,2) END +'月'+''' '
FROM (SELECT DISTINCT [date] FROM Test) a
SET @Sql=@Sql+' FROM Test group by id,name order by id'
EXEC (@sql)
-----------------------------------------------------------------结果-------------------------------------------------
/*
id name 1月 2月 3月 4月 5月
----------- ---------- -------------------- -------------------- -------------------- -------------------- --------------------
1 张三 11233 22331 442 234
2 李四 11233 22331 442 234
3 王五 11233 22331 442 234 22114
*/
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-05-20 13:18:35
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(4),[date] int,[value] int)
insert [tb]
select 1,'张三',201301,11233 union all
select 1,'张三',201302,22331 union all
select 1,'张三',201303,442 union all
select 1,'张三',201304,234 union all
select 2,'李四',201301,11233 union all
select 2,'李四',201302,22331 union all
select 2,'李四',201303,442 union all
select 2,'李四',201304,234 union all
select 3,'王五',201301,11233 union all
select 3,'王五',201302,22331 union all
select 3,'王五',201303,442 union all
select 3,'王五',201304,234 union all
select 3,'王五',201305,22114
--------------开始查询--------------------------
select
id,name,
max(case when right(date,2)='01' then value else 0 end) as '1月',
max(case when right(date,2)='02' then value else 0 end) as '2月',
max(case when right(date,2)='03' then value else 0 end) as '3月',
max(case when right(date,2)='04' then value else 0 end) as '4月',
max(case when right(date,2)='05' then value else 0 end) as '5月',
max(case when right(date,2)='06' then value else 0 end) as '6月',
max(case when right(date,2)='07' then value else 0 end) as '月'
from
tb
group by
id,name
----------------结果----------------------------
/* id name 1月 2月 3月 4月 5月 6月 月
----------- ---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2 李四 11233 22331 442 234 0 0 0
3 王五 11233 22331 442 234 22114 0 0
1 张三 11233 22331 442 234 0 0 0(3 行受影响)*/