SQL行列转换解决思路
SQL行列转换
找高人解决一下难题,越简单越好。
原表是这样的
Contract No Production Order No FID CABNO type Budget
322803-001 800007423906 UCYCOCC11583 Cabinet 2 Assembling 70
322803-001 800007423906 UCYCOCC11583 Cabinet 2 MaterPreparatio 6
322803-001 800007423906 UCYCOCC11583 Cabinet 2 Testing 20
322803-001 800007423906 UCYCOCC11583 Cabinet 2 Wiring 210
希望的格式是这样的
ContractNo Production Orde FID CabNO Materia Assembly Wiring Testing
322803-001 800007423906 UCYCOCC11583 Cabinet 2 6 70 210 20
type只有这四种。一个contract no包括多个production order,一个production order包含多个FID。
其中production order不止一个,FID也不止一个。
------解决方案--------------------
------解决方案--------------------
借用一下上面的造数脚本
找高人解决一下难题,越简单越好。
原表是这样的
Contract No Production Order No FID CABNO type Budget
322803-001 800007423906 UCYCOCC11583 Cabinet 2 Assembling 70
322803-001 800007423906 UCYCOCC11583 Cabinet 2 MaterPreparatio 6
322803-001 800007423906 UCYCOCC11583 Cabinet 2 Testing 20
322803-001 800007423906 UCYCOCC11583 Cabinet 2 Wiring 210
希望的格式是这样的
ContractNo Production Orde FID CabNO Materia Assembly Wiring Testing
322803-001 800007423906 UCYCOCC11583 Cabinet 2 6 70 210 20
type只有这四种。一个contract no包括多个production order,一个production order包含多个FID。
其中production order不止一个,FID也不止一个。
------解决方案--------------------
只有4种TYPE就用静态的了。
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-09-25 16:05:40
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ContractNo] varchar(10),[Production] bigint,[OrderNo] varchar(12),[FID] varchar(7),[CABNO] int,[type] varchar(15),[Budget] int)
insert [tb]
select '322803-001',800007423906,'UCYCOCC11583','Cabinet',2,'Assembling',70 union all
select '322803-001',800007423906,'UCYCOCC11583','Cabinet',2,'MaterPreparatio',6 union all
select '322803-001',800007423906,'UCYCOCC11583','Cabinet',2,'Testing',20 union all
select '322803-001',800007423906,'UCYCOCC11583','Cabinet',2,'Wiring',210
--------------开始查询--------------------------
SELECT
ContractNo ,
Production ,
OrderNo ,
fid ,
cabno ,
MAX(CASE WHEN type='MaterPreparatio' THEN Budget ELSE ''END) AS Materia ,
MAX(CASE WHEN type='Assembling' THEN Budget ELSE ''END) AS [Assembly] ,
MAX(CASE WHEN type='Wiring' THEN Budget ELSE '' END) AS Wiring ,
MAX(CASE WHEN type='Testing' THEN Budget ELSE ''END) AS Testing
FROM
[tb]
GROUP BY
ContractNo ,
Production ,
OrderNo ,
fid ,
cabno
----------------结果----------------------------
/* ContractNo Production OrderNo fid cabno Materia Assembly Wiring Testing
---------- -------------------- ------------ ------- ----------- ----------- ----------- ----------- -----------
322803-001 800007423906 UCYCOCC11583 Cabinet 2 6 70 210 20
(1 行受影响)
*/
------解决方案--------------------
借用一下上面的造数脚本
--create table #tb (id int ,[ContractNo] varchar(10),[Production] bigint,[OrderNo] varchar(12),[FID] varchar(7),[CABNO] int,[type] varchar(15),[Budget] int)
--insert into #tb
--select 1,'322803-001',800007423906,'UCYCOCC11583','Cabinet',2,'Assembling',70 union all
--select 1,'322803-001',800007423906,'UCYCOCC11583','Cabinet',2,'MaterPreparatio',6 union all
--select 1,'322803-001',800007423906,'UCYCOCC11583','Cabinet',2,'Testing',20 union all
--select 1,'322803-001',800007423906,'UCYCOCC11583','Cabinet',2,'Wiring',210
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([type])+'=max(case when [ContractNo]='+quotename(ContractNo,'''')+' then [Budget] else 0 end)'
from #tb group by [type],ContractNo
exec('select [ContractNo],Production,OrderNo,FID,CABNO'+@s+' from #tb group by [ContractNo],Production,OrderNo,FID,CABNO')
/*
ContractNo Production OrderNo FID CABNO Assembling MaterPreparatio Testing Wiring