SQL有关问题求教，

2013-10-09

SQL问题求教，急，在线等原格式是这样的Contract NoProduction Order NoFIDCABNOtypebudgetchange_id722a24-

SQL问题求教，急，在线等
原格式是这样的

Contract NoProduction Order NoFIDCABNOtypebudgetchange_id
722a24-001800008330981UCYD6CC14196+91AA01Assembling9ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330981UCYD6CC14196+91AA01Testing1ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330981UCYD6CC14196+91AA01Wiring8.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330982UCYD6CC14197+91AA02Assembling9ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330982UCYD6CC14197+91AA02Testing1ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330982UCYD6CC14197+91AA02Wiring8.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330983UCYD6CC14198+91AA03Assembling9ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330983UCYD6CC14198+91AA03Testing1ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330983UCYD6CC14198+91AA03Wiring8.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330985UCYD6CC14199+91AA04Assembling9ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330985UCYD6CC14199+91AA04Testing1ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330985UCYD6CC14199+91AA04Wiring8.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330986UCYD6CC14200+91AA05Assembling9ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330986UCYD6CC14200+91AA05Testing1ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330986UCYD6CC14200+91AA05Wiring8.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330987UCYD6CC14201+91AA06Assembling9ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330987UCYD6CC14201+91AA06Testing1ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330987UCYD6CC14201+91AA06Wiring8.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330988UCYD6CC14202+91AA07Assembling9ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330988UCYD6CC14202+91AA07Testing1ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330988UCYD6CC14202+91AA07Wiring8.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330990UCYD6CC14203+91AA08Assembling9ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330990UCYD6CC14203+91AA08Testing1ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330990UCYD6CC14203+91AA08Wiring8.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330991UCYD6CC14204+PUMP01Assembling9ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330991UCYD6CC14204+PUMP01Testing1ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330991UCYD6CC14204+PUMP01Wiring8.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75

希望是这样的格式

Contract NoProduction Order NoFIDCABNOAssemblingTestingWiringchange_id
722a24-001800008330981UCYD6CC14196+91AA01918.5ADB0285F-FE9F-495A-BAE5-C9504CD59F75
722a24-001800008330982UCYD6CC14197+91AA02918.5ADB0285F-FE9F-495A-BAE5-C9504CD59F76
722a24-001800008330983UCYD6CC14198+91AA03918.5ADB0285F-FE9F-495A-BAE5-C9504CD59F77
722a24-001800008330985UCYD6CC14199+91AA04918.5ADB0285F-FE9F-495A-BAE5-C9504CD59F78
722a24-001800008330986UCYD6CC14200+91AA05918.5ADB0285F-FE9F-495A-BAE5-C9504CD59F79
722a24-001800008330987UCYD6CC14201+91AA06918.5ADB0285F-FE9F-495A-BAE5-C9504CD59F80
722a24-001800008330988UCYD6CC14202+91AA07918.5ADB0285F-FE9F-495A-BAE5-C9504CD59F81
722a24-001800008330990UCYD6CC14203+91AA08918.5ADB0285F-FE9F-495A-BAE5-C9504CD59F82
722a24-001800008330991UCYD6CC14204+PUMP01918.5ADB0285F-FE9F-495A-BAE5-C9504CD59F83

sql
[解决办法]

select '722a24-001','800008330982','UCYD6CC14197','+91AA02','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330983','UCYD6CC14198','+91AA03','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330983','UCYD6CC14198','+91AA03','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330983','UCYD6CC14198','+91AA03','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330985','UCYD6CC14199','+91AA04','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330985','UCYD6CC14199','+91AA04','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330985','UCYD6CC14199','+91AA04','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330986','UCYD6CC14200','+91AA05','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330986','UCYD6CC14200','+91AA05','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330986','UCYD6CC14200','+91AA05','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330987','UCYD6CC14201','+91AA06','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330987','UCYD6CC14201','+91AA06','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330987','UCYD6CC14201','+91AA06','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330988','UCYD6CC14202','+91AA07','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330988','UCYD6CC14202','+91AA07','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330988','UCYD6CC14202','+91AA07','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330990','UCYD6CC14203','+91AA08','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330990','UCYD6CC14203','+91AA08','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330990','UCYD6CC14203','+91AA08','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330991','UCYD6CC14204','+PUMP01','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330991','UCYD6CC14204','+PUMP01','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330991','UCYD6CC14204','+PUMP01','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75'
--------------开始查询--------------------------

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([type])+'=max(case when [type]='+quotename([type],'''')+' then [budget] else ''0'' end)'
from [huang] group by [type]
exec('select [ContractNo],[ProductionOrderNo],[FID],[CABNO] ,change_id'+@s+' from [huang] group by [ContractNo],[ProductionOrderNo],[FID],[CABNO],change_id')
----------------结果----------------------------
/*
ContractNo    ProductionOrderNo FID             CABNO      change_id                            Assembling Testing Wiring
------------- ----------------- --------------- ---------- ------------------------------------ ---------- ------- ------
722a24-001    800008330981      UCYD6CC14196    +91AA01    ADB0285F-FE9F-495A-BAE5-C9504CD59F75 9          1       8.5
722a24-001    800008330982      UCYD6CC14197    +91AA02    ADB0285F-FE9F-495A-BAE5-C9504CD59F75 9          1       8.5
722a24-001    800008330983      UCYD6CC14198    +91AA03    ADB0285F-FE9F-495A-BAE5-C9504CD59F75 9          1       8.5
722a24-001    800008330985      UCYD6CC14199    +91AA04    ADB0285F-FE9F-495A-BAE5-C9504CD59F75 9          1       8.5
722a24-001    800008330986      UCYD6CC14200    +91AA05    ADB0285F-FE9F-495A-BAE5-C9504CD59F75 9          1       8.5
722a24-001    800008330987      UCYD6CC14201    +91AA06    ADB0285F-FE9F-495A-BAE5-C9504CD59F75 9          1       8.5

722a24-001    800008330988      UCYD6CC14202    +91AA07    ADB0285F-FE9F-495A-BAE5-C9504CD59F75 9          1       8.5
722a24-001    800008330990      UCYD6CC14203    +91AA08    ADB0285F-FE9F-495A-BAE5-C9504CD59F75 9          1       8.5
722a24-001    800008330991      UCYD6CC14204    +PUMP01    ADB0285F-FE9F-495A-BAE5-C9504CD59F75 9          1       8.5
*/

[解决办法]
我这里加了一条ADB0285F-FE9F-495A-BAE5-C9504CD59F76的数据，用于展现where条件是否有效

----------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-10-07 16:27:57
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--    Jun 10 2013 20:09:10
--    Copyright (c) Microsoft Corporation
--    Enterprise Evaluation 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]([ContractNo] varchar(13),[ProductionOrderNo] varchar(15),[FID] varchar(15),[CABNO] varchar(10),[type] varchar(13),[budget] varchar(6),[change_id] uniqueidentifier)
insert [huang]
select '722a24-001','800008330981','UCYD6CC14196','+91AA01','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330981','UCYD6CC14196','+91AA01','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330981','UCYD6CC14196','+91AA01','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330982','UCYD6CC14197','+91AA02','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330982','UCYD6CC14197','+91AA02','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330982','UCYD6CC14197','+91AA02','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330983','UCYD6CC14198','+91AA03','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330983','UCYD6CC14198','+91AA03','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330983','UCYD6CC14198','+91AA03','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330985','UCYD6CC14199','+91AA04','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330985','UCYD6CC14199','+91AA04','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330985','UCYD6CC14199','+91AA04','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330986','UCYD6CC14200','+91AA05','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330986','UCYD6CC14200','+91AA05','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330986','UCYD6CC14200','+91AA05','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330987','UCYD6CC14201','+91AA06','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330987','UCYD6CC14201','+91AA06','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330987','UCYD6CC14201','+91AA06','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330988','UCYD6CC14202','+91AA07','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330988','UCYD6CC14202','+91AA07','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330988','UCYD6CC14202','+91AA07','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330990','UCYD6CC14203','+91AA08','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330990','UCYD6CC14203','+91AA08','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330990','UCYD6CC14203','+91AA08','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330991','UCYD6CC14204','+PUMP01','Assembling','9','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330991','UCYD6CC14204','+PUMP01','Testing','1','ADB0285F-FE9F-495A-BAE5-C9504CD59F75' union all
select '722a24-001','800008330991','UCYD6CC14204','+PUMP01','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F75'

union all
select '722a24-001','800008330991','UCYD6CC14204','+PUMP01','Wiring','8.5','ADB0285F-FE9F-495A-BAE5-C9504CD59F76'
--------------开始查询--------------------------
DECLARE @change_id UNIQUEIDENTIFIER
SET @change_id=N'ADB0285F-FE9F-495A-BAE5-C9504CD59F76'

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([type])+'=max(case when [type]='+quotename([type],'''')+' then [budget] else ''0'' end)'
from [huang] group by [type]
EXEC ('select [ContractNo],[ProductionOrderNo],[FID],[CABNO] ,change_id'+@s+' from [huang] WHERE change_id='+''''+@change_id+''''+'  group by [ContractNo],[ProductionOrderNo],[FID],[CABNO],change_id')
----------------结果----------------------------
/*
ContractNo    ProductionOrderNo FID             CABNO      change_id                            Assembling Testing Wiring
------------- ----------------- --------------- ---------- ------------------------------------ ---------- ------- ------
722a24-001    800008330991      UCYD6CC14204    +PUMP01    ADB0285F-FE9F-495A-BAE5-C9504CD59F76 0          0       8.5
*/

[解决办法]
type多少种没关系，我那个是动态的，但是排序的话，你又要m在第一个，这个不符合默认的排序规则
[解决办法]

SELECT @S='上面拼接那些代码'
FROM 
SYSCOLUMNS WHERE ID=OBJECT_ID('huang') 
ORDER BY case when name='Material Preparation' then 1 
              when ....
          end

热点排行