挺复杂的一个问题，兄弟们来帮帮忙

Question

CREATE TABLE [dbo].[FeeProject](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FPName] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[Rate] [decimal](18, 4) NULL,
[bEnabled] [bit] NULL
) ON [PRIMARY]GOCREATE TABLE [dbo].[Materials](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Model] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Specifications] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[Size] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Materials] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
Materials表2 CD111011 100U/F16Vv1 5*11*11FeeProject表3 水电费 3.5000 True
2 人工费 1.0000 True
4 制造费用 5.0000 True
9 test 11.0000 False
5 期间费用 5.0000 True
NULL NULL NULL NULL希望得到的结果是型号     规格        尺寸    材料成本   人工费  水电费  制造费用  期间费用
CD111011 100u/F16Vv1 5*11*11  685.4612      1     3.5        5        5（Materials与FeeProject没有关系，只是想在Materials表中增加其它费用列（行列转换后））

Answer 1

/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------

Answer 2

表datatable: 
d_id    d_user    d_type    d_amount 
1        张三        新做        1500 
2        李四        新做        1600 
3        张三        改版        1100 
4        李四        改版        1000 
5        张三        返工        800 
6        李四        返工        700 
7        张三        新做        900 
8        李四        返工        650 想要统计结果(按合计排序): 
姓名      新做        改版        返工        合计 
李四      1600      1000        1350      3950 
张三      2400      1100        800      4300 CREATE table datatable(d_id int,d_user nvarchar(10),d_type nvarchar(10),d_amount int)insert datatable
select 1 , '张三', '新做', 1500 union all
select 2 , '李四', '新做', 1600 union all
select 3 , '张三', '改版', 1100 union all
select 4 , '李四', '改版', 1000 union all
select 5 , '张三', '返工', 800 union all
select 6 , '李四', '返工', 700 union all
select 7 , '张三', '新做', 900 union all
select 8 , '李四', '返工', 650--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select d_user '
select @sql = @sql + ' , max(case d_type when ''' + d_type + ''' then d_amount else 0 end) [' + d_type + ']'
from (select distinct d_type from datatable) as a
set @sql = @sql + ' , sum(d_amount) 合计 from datatable group by d_user'
exec(@sql)/*
d_user     返工          改版          新做          合计
---------- ----------- ----------- ----------- -----------
李四         700         1000        1600        3950
张三         800         1100        1500        4300
*/

Answer 3

型号    规格        尺寸    材料成本  人工费  水电费  制造费用  期间费用 
CD111011 100u/F16Vv1 5*11*11  685.4612      1    3.5        5        5 

Answer 4

to jack_ailly
 
现在问题不是行列转换，而是把FeeProject行列转换后把结果当成列加在Materials中的所有行

Answer 5

拿到行转列后，再把几列的内容字符加起来update进去不就行了吗？

Answer 6

成本结果在表中是有这个字段的，可以去掉这个字段,不影响如：
型号         规格         尺寸      人工费  水电费  制造费用  期间费用 
CD111011 100u/F16Vv1 5*11*11       1    3.5        5        5 
CD111111 100u        5*22*22       1    3.5        5        5
CD112211 100u2       5*22*22       1    3.5        5        5
CD111111 100u3       5*22*22       1    3.5        5        5
我的意思就是在Materials中增加（Feeproject表行列转换后）列

Answer 7

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-23 13:43:09
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[Materials]
if object_id('[Materials]') is not null drop table [Materials]
go 
create table [Materials]([col1] int,[col2] varchar(8),[col3] varchar(11),[col4] varchar(7))
insert [Materials]
select 2,'CD111011','100U/F16Vv1','5*11*11'
--> 测试数据：[FeeProject]
if object_id('[FeeProject]') is not null drop table [FeeProject]
go 
create table [FeeProject]([col1] int,[col2] varchar(8),[col3] numeric(6,4),[col4] varchar(5))
insert [FeeProject]
select 3,'水电费',3.5000,'True' union all
select 2,'人工费',1.0000,'True' union all
select 4,'制造费用',5.0000,'True' union all
select 9,'test',11.0000,'False' union all
select 5,'期间费用',5.0000,'True'
--------------开始查询--------------------------
select
 a.*,
 max(case when b.col2='水电费' then b.col3 else 0 end) as '水电费',
 max(case when b.col2='人工费' then b.col3 else 0 end) as '人工费',
 max(case when b.col2='制造费用' then b.col3 else 0 end) as '制造费用',
 max(case when b.col2='期间费用' then b.col3 else 0 end) as '期间费用'
from
 [Materials] a,
 (select id='2', * from [FeeProject])b
where
 a.col1=b.id
and
 b.col4='true'
group by
 a.col1,a.col2,a.col3,a.col4
----------------结果----------------------------
/*col1        col2     col3        col4    水电费                                     人工费                                     制造费用                                    期间费用
----------- -------- ----------- ------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2           CD111011 100U/F16Vv1 5*11*11 3.5000                                  1.0000                                  5.0000                                  5.0000(1 行受影响)
 
*/

Answer 8

忘了说了，表FeeProject中的记录是可以增加的，就是说要动态行列转换

Answer 9

ID     型号        规格        尺寸      人工费  水电费  制造费用  期间费用 
2    CD111011 100u/F16Vv1 5*11*11      1    3.5        5        5 
3    CD111111 100u        5*22*22      1    3.5        5        5 
4    CD112211 100u2      5*22*22      1    3.5        5        5 
5    CD111111 100u3      5*22*22      1    3.5        5        5 
表FeeProject记录不是固定的

Answer 10

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-23 13:43:09
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[Materials]
if object_id('[Materials]') is not null drop table [Materials]
go 
create table [Materials]([col1] int,[col2] varchar(8),[col3] varchar(11),[col4] varchar(7))
insert [Materials]
select 2,'CD111011','100U/F16Vv1','5*11*11'
--> 测试数据：[FeeProject]
if object_id('[FeeProject]') is not null drop table [FeeProject]
go 
create table [FeeProject]([col1] int,[col2] varchar(8),[col3] numeric(6,4),[col4] varchar(5))
insert [FeeProject]
select 3,'水电费',3.5000,'True' union all
select 2,'人工费',1.0000,'True' union all
select 4,'制造费用',5.0000,'True' union all
select 9,'test',11.0000,'False' union all
select 5,'期间费用',5.0000,'True'
--------------开始查询--------------------------
/*select
 a.*,
 max(case when b.col2='水电费' then b.col3 else 0 end) as '水电费',
 max(case when b.col2='人工费' then b.col3 else 0 end) as '人工费',
 max(case when b.col2='制造费用' then b.col3 else 0 end) as '制造费用',
 max(case when b.col2='期间费用' then b.col3 else 0 end) as '期间费用'
from
 [Materials] a,
 (select id='2', * from [FeeProject])b
where
 a.col1=b.id
and
 b.col4='true'
group by
 a.col1,a.col2,a.col3,a.col4*/declare @sql varchar(8000)
set @sql = 'select a.col1,a.col2,a.col3,a.col4 '
select @sql = @sql + ' , max(case b.col2 when ''' + b.col2 + ''' then b.col3 else 0 end) [' + b.col2 + ']'
from 
 [Materials] a,
(select id=row_number()over(order by getdate()),* from [FeeProject])b 
set @sql = @sql + 'from  [Materials] a,
(select id=row_number()over(order by getdate()),* from [FeeProject])b  where a.col1=b.id group by a.col1,a.col2,a.col3,a.col4'
exec(@sql) ----------------结果----------------------------
/*col1        col2     col3        col4    水电费                                     人工费                                     制造费用                                    test                                    期间费用
----------- -------- ----------- ------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2           CD111011 100U/F16Vv1 5*11*11 0.0000                                  1.0000                                  0.0000                                  0.0000                                  0.0000(1 行受影响)*/

Answer 11

大哥，你应该是用oracle的吗，如果是SQL2005应该怎么写啊

Answer 12

我根据你的写法用SQL2005改之后
declare @sql varchar(4000) 
set @sql = 'select a.id,a.model' 
select @sql = @sql + ' , max(case b.fpname when ''' + b.fpname + ''' then b.rate else 0 end) [' + b.fpname + ']' 
from [Materials] a,
(select distinct fpname from feeproject where benabled=1) b 
-- set @sql=left(@sql,len(@sql)-1)
print @sql
set @sql = @sql + ' from [materials] a,
(select * from feeproject where benabled=1) b group by a.id,a.model' 
exec(@sql)
但是结果不是很对
ID 型号        [期间费用] [期间费用] [人工费] [人工费] [水电费] [水电费] [制造费用][制造费用]
2  CD111011 5.0000 5.0000 1.0000 1.0000 3.5000 3.5000 5.0000 5.0000
3  testt 5.0000 5.0000 1.0000 1.0000 3.5000 3.5000 5.0000 5.0000出现重复了，应该怎么改啊

Answer 13

如果Materials表有多条数据时会重复吗

Answer 14

declare @sql varchar(4000) 
set @sql = 'select a.id,a.msid,a.model' 
select @sql = @sql + ' , max(case b.fpname when ''' + b.fpname + ''' then b.rate else 0 end) ['+b.fpname+']' 
from [Materials] a,
(select tid=row_number()over(order by getdate()),* from feeproject where benabled=1) b
set @sql = @sql + ' from [materials] a,
(select tid=row_number()over(order by getdate()),* from feeproject where benabled=1) b where a.id=b.tid group by a.id,a.msid,a.model' 
exec(@sql)
结果字段重复了，怎么解决啊，

Answer 15

要判断col='true' 这个条件写上没?

Answer 16

写了啊，那个字段是判断是否启用这个收费项目的，就是我写的benabled=1

Answer 17

非常感谢各位的帮忙，之后我修改了一下，达到要求了，但还不是太好，希望高手们来看看应该怎样处理更好一点。declare @sql varchar(4000) 
set @sql = 'select' 
select @sql = @sql + ' max(case fpname when ''' + fpname + ''' then rate else 0 end) [' + fpname + '],' 
from (select * from feeproject where benabled=1) as a 
set @sql = @sql + ' a.id,a.msid,a.model from [materials] a,
(select * from feeproject where benabled=1) b group by a.id,a.msid,a.model' 
exec(@sql)
就快到圣诞节了，加点分给大家，下午结贴，大家多捧场哦