交叉报表,找了很久,没找到想要的,解决就结贴.

Question

求交叉表的SQL,三个表,结果如下
(物品(假设3个),客户(假设3个),销售发货表(假设9条))要求结果如4.解决就结贴.
-------------------------------------------1,物品表: UB_ArticlesArticleID  ArticleCode  ArticleName
INT(4)     CHAR(10)     CHAR(10)
1          1            物品1
2          2            物品2
3          3            物品3
.          .            .
.          .            .
N          N            物品N
(更多物品)-------------------------------------------2,客户表: UB_CustomersCustomerID  CustomerCode  CustomerName
INT(4)      CHAR(10)      CHAR(10)
1           1             客户1
2           2             客户2
3           3             客户3
.          .              .
.          .              .
N          N              客户N
(更多客户)-------------------------------------------3,销售发货主表: UD_SaleInvoicesSaleInvoiceID  ArticleID  CustomerID  Amount
INT(4)         INT(4)     INT(4)      INT(4)
1              1          1           11
2              1          2           12
3              1          3           13
1              2          1           21
2              2          2           22
3              2          3           23
1              3          1           31
2              3          2           32
3              3          3           33
.              .          .           .
.              .          .           .
.              .          .           .
(更多单据)-------------------------------------------4,要得到结果:ArticleCode  ArticleName  客户1  客户2  客户3 ........(更多客户)
1            物品1        11     12     13
2            物品2        21     22     23
3            物品3        31     32     33
.            .            .      .      .
.            .            .      .      .
.            .            .      .      .
.            .            .      .      .
(更多物品)-------------------------------------------

Answer 1

最好给出优化的 SQL,
对了,看了很多 SQL 有临时表,这是为什么?

Answer 2

if object_id('UB_Articles') is not null
    drop table UB_Articles
if object_id('UB_Customers') is not null
    drop table UB_Customers
if object_id('UD_SaleInvoices') is not null
    drop table UD_SaleInvoices
GO
----创建测试数据
create table UB_Articles (ArticleID int,ArticleCode char(10),ArticleName char(10))
insert UB_Articles
select 1,          '1',            '物品1' union all
select 2,          '2',            '物品2' union all
select 3,          '3',            '物品3' 
create table UB_Customers(CustomerID int,CustomerCode char(10),CustomerName char(10))
insert UB_Customers
select 1,           '1',             '客户1' union all
select 2,           '2',             '客户2' union all
select 3,           '3',             '客户3'
create table UD_SaleInvoices(SaleInvoiceID int,ArticleID int,CustomerID int,Amount int)
insert UD_SaleInvoices
select 1,              1,          1,           11 union all
select 2,              1,          2,           12 union all
select 3,              1,          3,           13 union all
select 1,              2,          1,           21 union all
select 2,              2,          2,           22 union all
select 3,              2,          3,           23 union all
select 1,              3,          1,           31 union all
select 2,              3,          2,           32 union all
select 3,              3,          3,           33
GO
----构建行转列SQL
declare @sql varchar(8000)set @sql = ''
select @sql = @sql + ',['+ b.CustomerName + '] = sum(case CustomerID when ' + rtrim(a.CustomerID) + ' then Amount else 0 end)'
from UD_SaleInvoices as a left join UB_Customers as b on a.CustomerID = b.CustomerID group by a.CustomerID,b.CustomerNameset @sql = 'select b.ArticleCode,b.ArticleName' + @sql + ' from UD_SaleInvoices as a 
left join UB_Articles as b on a.ArticleID = b.ArticleID group by b.ArticleCode,b.ArticleName'
--打印SQL
print @sql
--执行SQL
EXEC(@sql)----清除测试环境
drop table UB_Articles,UB_Customers,UD_SaleInvoices/*结果:
ArticleCode  ArticleName  客户1  客户2  客户3
-------------------------------------------------
1            物品1        11     12     13
2            物品2        21     22     23
3            物品3        31     32     33
*/

Answer 3

--物品表: UB_Articles
create table UB_Articles(ArticleID int, ArticleCode char(10), ArticleName char(10))
insert UB_Articles select 1,          '1',            '物品1'
union all select 2,          '2',            '物品2'
union all select 3,          '3',            '物品3'
--客户表: UB_Customers
create table UB_Customers(CustomerID int, CustomerCode char(10), CustomerName char(10))
insert UB_Customers select 1,           '1',             '客户1'
union all select 2,           '2',             '客户2'
union all select 3,           '3',             '客户3'
--销售发货主表: UD_SaleInvoices
create table UD_SaleInvoices(SaleInvoiceID int, ArticleID int, CustomerID int, Amount int)
insert UD_SaleInvoices select 1,              1,          1,           11
union all select 2,              1,          2,           12
union all select 3,              1,          3,           13
union all select 1,              2,          1,           21
union all select 2,              2,          2,           22
union all select 3,              2,          3,           23
union all select 1,              3,          1,           31
union all select 2,              3,          2,           32
union all select 3,              3,          3,           33declare @sql nvarchar(4000)
set @sql='select UD_SaleInvoices.ArticleID, ArticleName, '
select @sql=@sql+quotename(rtrim(ltrim(CustomerName)))+'=sum(case when CustomerID='+quotename(CustomerID, '''')+' then Amount end),'
from UB_Customersselect @sql=left(@sql, len(@sql)-1), 
@sql=@sql+' from UD_SaleInvoices inner join UB_Articles on UD_SaleInvoices.ArticleID=UB_Articles.ArticleID group by UD_SaleInvoices.ArticleID, ArticleName '
exec(@sql)

Answer 4

--result
ArticleID   ArticleName 客户1         客户2         客户3         
----------- ----------- ----------- ----------- ----------- 
1           物品1         11          12          13
2           物品2         21          22          23
3           物品3         31          32          33

Answer 5

--物品表
create table UB_Articles
(
ArticleID   int,
ArticleCode varchar(10) ,
ArticleName varchar(10)
)
insert into UB_Articles(ArticleID,ArticleCode,ArticleName) values(1,'1','物品1')
insert into UB_Articles(ArticleID,ArticleCode,ArticleName) values(2,'2','物品2')
insert into UB_Articles(ArticleID,ArticleCode,ArticleName) values(3,'3','物品3')--客户表
create table UB_Customers
(
CustomerID   int,
CustomerCode varchar(10) ,
CustomerName varchar(10)
)
insert into UB_Customers(CustomerID,CustomerCode,CustomerName) values(1,'1','客户1')
insert into UB_Customers(CustomerID,CustomerCode,CustomerName) values(2,'2','客户2')
insert into UB_Customers(CustomerID,CustomerCode,CustomerName) values(3,'3','客户3')--销售发货主表
create table UD_SaleInvoices
(
SaleInvoiceID int,
ArticleID     int,
CustomerID    int,
Amount        int
)
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Amount) values(1,1,1,11)
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Amount) values(2,1,2,12)
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Amount) values(3,1,3,13)
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Amount) values(1,2,1,21)
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Amount) values(2,2,2,22)
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Amount) values(3,2,3,23)
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Amount) values(1,3,1,31)
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Amount) values(2,3,2,32)
insert into UD_SaleInvoices(SaleInvoiceID,ArticleID,CustomerID,Amount) values(3,3,3,33)select ArticleCode , ArticleName , CustomerName , Amount
into tmp
from UB_Articles a ,UB_Customers b ,UD_SaleInvoices c
where a.articleid = c.articleid and b.customerid = c.customeriddeclare @sql varchar(8000)
set @sql = 'select ArticleCode , ArticleName'
select @sql = @sql + ' , max(case CustomerName when ''' + CustomerName + ''' then amount else 0 end) [' + CustomerName + ']'
from (select distinct CustomerName from tmp) as a
set @sql = @sql + ' from tmp group by ArticleCode , ArticleName'
exec(@sql) drop table UB_Articles,UB_Customers,UD_SaleInvoices,tmp/*result
ArticleCode ArticleName 客户1         客户2         客户3         
----------- ----------- ----------- ----------- ----------- 
1           物品1         11          12          13
2           物品2         21          22          23
3           物品3         31          32          33
*/