--行转列 SELECT cardcode ,ISNULL(SUM(CASE WHEN c_type='主合同'THEN price END),0)[主合同] ,ISNULL(SUM(CASE WHEN c_type='增项合同'THEN price END),0)[增项合同] ,ISNULL(SUM(CASE WHEN c_type='减项合同'THEN price END),0)[减项合同] --... FROM [test] GROUP BY cardcode
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2015-01-12 11:21:15 -- Version: -- Microsoft SQL Server 2014 - 12.0.2254.0 (X64) -- Jul 25 2014 18:52:51 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.4 <X64> (Build 9860: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[test] if object_id('[test]') is not null drop table [test] go create table [test]([cardcode] int,[c_type] varchar(8),[price] int) insert [test] select 1,'主合同',10 union all select 1,'增项合同',10 union all select 2,'主合同',25 union all select 2,'减项合同',20 --------------开始查询--------------------------
declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename([c_type])+'=max(case when [cardcode]='+quotename([cardcode],'''')+' then [price] else 0 end)' from [test] group by [cardcode],[c_type] order by [c_type] desc exec('select [cardcode]'+@s+' from [test] group by [cardcode]') ----------------结果---------------------------- /* cardcode 主合同 主合同 增项合同 减项合同 ----------- ----------- ----------- ----------- ----------- 1 10 0 10 0 2 0 25 0 25 */
SELECT cardcode
,ISNULL(SUM(CASE WHEN c_type='主合同'THEN price END),0)[主合同]
,ISNULL(SUM(CASE WHEN c_type='增项合同'THEN price END),0)[增项合同]
,ISNULL(SUM(CASE WHEN c_type='减项合同'THEN price END),0)[减项合同]
--...
FROM [test]
GROUP BY cardcode
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2015-01-12 11:21:15
-- Version:
-- Microsoft SQL Server 2014 - 12.0.2254.0 (X64)
-- Jul 25 2014 18:52:51
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.4 <X64> (Build 9860: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([cardcode] int,[c_type] varchar(8),[price] int)
insert [test]
select 1,'主合同',10 union all
select 1,'增项合同',10 union all
select 2,'主合同',25 union all
select 2,'减项合同',20
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([c_type])+'=max(case when [cardcode]='+quotename([cardcode],'''')+' then [price] else 0 end)'
from [test] group by [cardcode],[c_type] order by [c_type] desc
exec('select [cardcode]'+@s+' from [test] group by [cardcode]')
----------------结果----------------------------
/*
cardcode 主合同 主合同 增项合同 减项合同
----------- ----------- ----------- ----------- -----------
1 10 0 10 0
2 0 25 0 25
*/