34,575
社区成员
发帖
与我相关
我的任务
分享
--表a
ReportID_u ReportColumnName_ch ItemID_i ColumnFormulaID_ch
1338e817-696c-440c-8702-fa2cf809e7f1 字段1 1 1
1338e817-696c-440c-8702-fa2cf809e7f1 字段2 2 3
1338e817-696c-440c-8702-fa2cf809e7f1 字段3 3 7
1338e817-696c-440c-8702-fa2cf809e7f1 字段4 4 9
…
--表b
ReportID_u OrgID_ch
1338e817-696c-440c-8702-fa2cf809e7f1 组织1
1338e817-696c-440c-8702-fa2cf809e7f1 组织2
1338e817-696c-440c-8702-fa2cf809e7f1 组织3
1338e817-696c-440c-8702-fa2cf809e7f1 组织4
1f9cd177-ce43-40bc-a378-021e4fde4f3d 组织5
…
--表c
OrgID_ch ItemID_i ColumnFormulaID_ch ColumnValue_ch
组织1 1 1 10
组织2 1 1 15
组织3 1 1 5
组织4 1 1 1
组织1 2 1 1
组织2 2 1 5
组织3 2 1 5
组织4 2 1 2
…
--要得到的结果:
--对应每一个ReportID_u,查询出一张表,其中第一列来自于表b,数据来自于表c的ColumnValue_ch值
OrgID_ch 字段1 字段2 字段3 字段4 ——表头
组织1 10 1 3 3
组织2
组织3
组织4
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-04 17:14:34
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ReportID_u] uniqueidentifier,[ReportColumnName_ch] varchar(5),[ItemID_i] int,[ColumnFormulaID_ch] int)
insert [a]
select '1338e817-696c-440c-8702-fa2cf809e7f1','字段1',1,1 union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','字段2',2,3 union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','字段3',3,7 union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','字段4',4,9
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ReportID_u] uniqueidentifier,[OrgID_ch] varchar(5))
insert [b]
select '1338e817-696c-440c-8702-fa2cf809e7f1','组织1' union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','组织2' union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','组织3' union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','组织4' union all
select '1f9cd177-ce43-40bc-a378-021e4fde4f3d','组织5'
--> 测试数据:[c]
if object_id('[c]') is not null drop table [c]
go
create table [c]([OrgID_ch] varchar(5),[ItemID_i] int,[ColumnFormulaID_ch] int,[ColumnValue_ch] int)
insert [c]
select '组织1',1,1,10 union all
select '组织2',1,1,15 union all
select '组织3',1,1,5 union all
select '组织4',1,1,1 union all
select '组织1',2,1,1 union all
select '组织2',2,1,5 union all
select '组织3',2,1,5 union all
select '组织4',2,1,2
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select b.OrgID_ch '
select @sql = @sql + ' , max(case a.ReportColumnName_ch when ''' + ReportColumnName_ch + ''' then c.ColumnValue_ch else 0 end) [' + ReportColumnName_ch + ']'
from (select distinct a.ReportColumnName_ch from a) as a
set @sql = @sql + ' from a,b,c where a.ReportID_u=b.ReportID_u and b.OrgID_ch=c.OrgID_ch group by b.OrgID_ch'
exec(@sql)
----------------结果----------------------------
/*OrgID_ch 字段1 字段2 字段3 字段4
-------- ----------- ----------- ----------- -----------
组织1 10 10 10 10
组织2 15 15 15 15
组织3 5 5 5 5
组织4 2 2 2 2
(4 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-04 17:14:34
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ReportID_u] uniqueidentifier,[ReportColumnName_ch] varchar(5),[ItemID_i] int,[ColumnFormulaID_ch] int)
insert [a]
select '1338e817-696c-440c-8702-fa2cf809e7f1','字段1',1,1 union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','字段2',2,3 union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','字段3',3,7 union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','字段4',4,9
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ReportID_u] uniqueidentifier,[OrgID_ch] varchar(5))
insert [b]
select '1338e817-696c-440c-8702-fa2cf809e7f1','组织1' union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','组织2' union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','组织3' union all
select '1338e817-696c-440c-8702-fa2cf809e7f1','组织4' union all
select '1f9cd177-ce43-40bc-a378-021e4fde4f3d','组织5'
--> 测试数据:[c]
if object_id('[c]') is not null drop table [c]
go
create table [c]([OrgID_ch] varchar(5),[ItemID_i] int,[ColumnFormulaID_ch] int,[ColumnValue_ch] int)
insert [c]
select '组织1',1,1,10 union all
select '组织2',1,1,15 union all
select '组织3',1,1,5 union all
select '组织4',1,1,1 union all
select '组织1',2,1,1 union all
select '组织2',2,1,5 union all
select '组织3',2,1,5 union all
select '组织4',2,1,2
--------------开始查询--------------------------
select
b.OrgID_ch,
max(case a.ReportColumnName_ch when '字段1' then c.ColumnValue_ch else 0 end) as 字段1,
max(case a.ReportColumnName_ch when '字段2' then c.ColumnValue_ch else 0 end) as 字段2,
max(case a.ReportColumnName_ch when '字段3' then c.ColumnValue_ch else 0 end) as 字段3,
max(case a.ReportColumnName_ch when '字段4' then c.ColumnValue_ch else 0 end) as 字段4
from
a,b,c
where
a.ReportID_u=b.ReportID_u
and
b.OrgID_ch=c.OrgID_ch
group by
b.OrgID_ch
----------------结果----------------------------
/*OrgID_ch 字段1 字段2 字段3 字段4
-------- ----------- ----------- ----------- -----------
组织1 10 10 10 10
组织2 15 15 15 15
组织3 5 5 5 5
组织4 2 2 2 2
(4 行受影响)
*/
select
b.OrgID_ch,
max(case a.ReportColumnName_ch when '字段1' then c.ColumnValue_ch else 0 end) as 字段1,
max(case a.ReportColumnName_ch when '字段2' then c.ColumnValue_ch else 0 end) as 字段2,
max(case a.ReportColumnName_ch when '字段3' then c.ColumnValue_ch else 0 end) as 字段3
from
a,b,c
where
a.ReportID_u=b.ReportID_u
and
b.OrgID_ch=c.OrgID_ch
group by
b.OrgID_ch
select
b.OrgID_ch,
max(case a.ReportColumnName_ch when '字段1' then c.ColumnValue_ch else 0 end) as 字段1,
max(case a.ReportColumnName_ch when '字段1' then c.ColumnValue_ch else 0 end) as 字段2,
max(case a.ReportColumnName_ch when '字段1' then c.ColumnValue_ch else 0 end) as 字段3
from
a,b,c
where
a.ReportID_u=b.ReportID_u
and
b.OrgID_ch=c.OrgID_ch
group by
b.OrgID_ch