像这种行转列+分类汇总的SQL语句怎么写？
--表共5层结构，逻辑关系是：客户-->项目-->业务单据-->具体单据数据<-->产品规格表以及合同表 本次只涉及其中4层（除客户），sql
--其中业务单据包含了进出关系(进是负，出是正），产品比较简单，就把品名和规格放在一张表里
--本次查询为了获得某客户下面几大产品的进出数据
/****** 对象: 项目表 脚本日期: 01/28/2013 08:09:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SteelProject](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NULL,--客户ID
[ProjectName] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL,--项目名称(仅
[Contractor] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ContactNumber] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[SecondParty] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[FirstParty] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[IsWancheng] [int] NULL,--项目状态,0为未完成,1已完成
CONSTRAINT [PK_SteelProject] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** 对象: 业务单据表（出库、入库单） 脚本日期: 01/28/2013 08:10:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SteelBusinessOrder](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NULL,--项目ID
[OrderNumber] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[OrderDirection] [int] NULL,--单据类型，0为出库，1为入库
[OrderDate] [smalldatetime] NULL,
[OrderClerk] [int] NULL,
[CustomReceiver] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[CustomOrderNumber] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[Carrier] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[IsDel] [int] NULL CONSTRAINT [DF_SteelBusinessOrder_IsDel] DEFAULT ((0)),--0为正常单据，1为作废单据
[Pyear] [char](4) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_BusinessOrder_Pyear] DEFAULT (CONVERT([char](4),getdate(),(112))),
[InsertTime] [smalldatetime] NULL CONSTRAINT [DF_SteelBusinessOrder_InsertTime] DEFAULT (CONVERT([char](20),getdate(),(120))),
CONSTRAINT [PK_BusinessOrder] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** 对象: 业务单据具体数据表 脚本日期: 01/28/2013 08:11:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SteelBusinessList](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,--业务单据
[StandardID] [int] NULL,--产品及规格表ID
[ListAmount] [int] NULL,
[SubTotal] [decimal](18, 2) NULL,--总数
CONSTRAINT [PK_SteelRecord] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
/****** 对象: 产品及规格表 脚本日期: 01/28/2013 08:20:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SteelStandard](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,--产品名称
[Standard] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,--规格
CONSTRAINT [PK_SteelStandard] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
/****** 对象: 合同表 脚本日期: 01/28/2013 08:22:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SteelAuditRecords](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NULL,--项目ID
[ContractConclude] [int] NULL,--合同签定情况，0为未签定，1为已签定
CONSTRAINT [PK_SteelContract] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--测试数据
truncate table SteelProject
truncate table SteelBusinessOrder
truncate table SteelBusinessList
truncate table SteelStandard
truncate table SteelAuditRecords
insert into SteelProject(customerID,projectName,isWancheng)
select 1,'农资市场一期工程' ,0--1
Union All select 1,'海洋产业研究院土建工程',0--2
Union All select 1,'丰收大地工程',1 --3
go
insert into SteelBusinessOrder(projectID,orderDirection,isDel)
select 1,0,0--1
Union All select 1,1,0--2
Union All select 1,1,0--3
Union All select 1,1,1--4
Union All select 2,0,0--5
Union All select 2,1,0--6
Union All select 2,0,0--7
Union All select 3,0,0--8
Union All select 3,1,0--9
go
insert into SteelBusinessList(orderID,standardID,subTotal)
select 1,1,100
Union All select 2,2,10.5
Union All select 3,5,100
Union All select 4,6,300
Union All select 1,8,100
Union All select 1,9,20.8
Union All select 3,1,300
Union All select 8,2,100
Union All select 8,6,100
Union All select 8,8,100
Union All select 9,3,100
Union All select 9,5,100
Union All select 9,10,100
insert into SteelStandard(ProductName,Standard)
select '钢管','1.0'--1
Union All select '钢管','1.2'--2
Union All select '钢管','1.3'--3
Union All select '钢管','1.5'--4
Union All select '扣件','十字'--5
Union All select '扣件','接卡'--6
Union All select '扣件','转向'--7
Union All select '套管','0.2'--8
Union All select '套管','0.3'--9
Union All select '钢管','3.0'--10
Union All select '钢管','4.0'--11
insert into SteelAuditRecords(ProjectID,ContractConclude)
select 1,0
Union All select 2,1
Union All select 3,1
with t
as(
select
a.ID as ProjectID,a.CustomerID,a.ProjectName,a.IsWancheng,
b.ID as OrderId,b.OrderDirection,b.IsDel,c.ContractConclude
from
[SteelProject] a
inner join
[SteelBusinessOrder] b
on
a.ID=b.ProjectID
inner join
[SteelAuditRecords] c
on
a.ID=c.ProjectID
),m
as(
select
a.OrderID,
a.StandardID,
a.SubTotal,
b.ProductName,
b.Standard
from
[SteelBusinessList] a
inner join
[SteelStandard] b
on
a.StandardID=b.ID
)
select
*
from
t
inner join
m
on
t.OrderId=m.OrderID
--目前已经把所有数据选出来了，如何按照下面的要求实现行转列并分类汇总呢？
--最终结果示例
--select 项目ID,名称，钢管出库__米，钢管入库__米，钢管剩余__米，扣件出库__只，扣件入库__只，扣件剩余__只，套管出库__米，套管入库__米，套管剩余__米,合同情况 where customerID=@customerID
