将交叉表查询转换为sql 2008 [英] convert crosstab query to sql 2008

问题描述

1-

 TRANSFORM Sum（ISSUE.Qty_issue） AS  [价值] 
  SELECT  ISSUE。[Tag_no]，Sum（ISSUE.Qty_issue） AS  [Total  Of  Qty_issue] 
  FROM  ISSUE 
  GROUP   BY  ISSUE。[Tag_ no] 
 PIVOT ISSUE.Tmpe; 

2-

 TRANSFORM Sum（MRV。[Ret-qty]） AS  [价值] 
  SELECT  MRV。[Tag_ no]，Sum（MRV。[Ret-qty]） AS  [总计  Ret-qty] 
  FROM  MRV 
  GROUP   BY  MRV。[Tag_ no] 
 PIVOT MRV.TEMP; 

3-

 TRANSFORM Sum（ REC。[Rec-qty]） AS  [值] 
  SELECT  REC。[Tag_ no]，Sum（REC。[Rec-qty]） AS  [总  Rec-qty] 
  FROM  REC 
  GROUP   BY  REC。[Tag_ no] 
 PIVOT REC.TMPE; 

解决方案

您需要了解 pivot [ ^ ]。



这组文章可能会有所帮助：

在SQL查询中使用Pivot的简单方法 [ ^ ]

如何执行数据透视操作T-SQL ..？ [ ^ ]

SQL - Pivot with Grand Total列和行 [ ^ ]

SQL数据透视表和表适配器 [ ^ ]



1.查询可以转换如下：

 < span class =code-keyword> SELECT  [Tag_n o]，[Val1]，[Val2]，[Val3] 
  FROM （
  SELECT  ISSUE。[Tag_no]，ISSUE.Tmpe，ISSUE.Qty_issue  AS  [Qty_issue] 
  FROM  ISSUE 
） AS  DT 
 PIVOT（（SUM（Qty_issue） FOR  Tmpe  IN （[Val1]，[Val2]，[Val3]）） AS  PVT; 

注意：我不知道返回什么类型的数据 ISSUE.Tmpe 字段。因此，您需要用适当的值替换 [Val1]，[Val2]，[Val3] 。

列出表格中的所有字段问题

  SELECT  ISSUE。[Tag_ no]，ISSUE。[ Miv_no]，ISSUE.Qty_issue，ISSUE。[TT-73_no]，ISSUE.Qty_issue，ISSUE。[TT-73_no]，ISSUE。[Rec_  by ]，ISSUE 。[Iso_ no]，ISSUE。 Double ，ISSUE.Tmpe 
  FROM  ISSUE; 

1-

TRANSFORM Sum(ISSUE.Qty_issue) AS [The Value]
SELECT ISSUE.[Tag_no], Sum(ISSUE.Qty_issue) AS [Total Of Qty_issue]
FROM ISSUE
GROUP BY ISSUE.[Tag_ no]
PIVOT ISSUE.Tmpe;

2-

TRANSFORM Sum(MRV.[Ret-qty]) AS [The Value]
SELECT MRV.[Tag_ no], Sum(MRV.[Ret-qty]) AS [Total Of Ret-qty]
FROM MRV
GROUP BY MRV.[Tag_ no]
PIVOT MRV.TEMP;

3-

TRANSFORM Sum(REC.[Rec-qty]) AS [The Value]
SELECT REC.[Tag_ no], Sum(REC.[Rec-qty]) AS [Total Of Rec-qty]
FROM REC
GROUP BY REC.[Tag_ no]
PIVOT REC.TMPE;

解决方案

You need to learn about pivot[^].

This set of articles might be helpful:
Simple Way To Use Pivot In SQL Query[^]
How to Perform Pivot Operation in T-SQL..?[^]
SQL - Pivot with Grand Total Column and Row[^]
SQL Pivot and Table Adapter[^]

1. query can be "converted" as follow:

SELECT [Tag_no], [Val1], [Val2], [Val3]
FROM (
    SELECT ISSUE.[Tag_no], ISSUE.Tmpe, ISSUE.Qty_issue AS [Qty_issue]
    FROM ISSUE
    ) AS DT
PIVOT ((SUM(Qty_issue) FOR Tmpe IN ([Val1], [Val2], [Val3])) AS PVT;

Note: i don't know what kind of data returns ISSUE.Tmpe field. So, you need to replace [Val1], [Val2], [Val3] with proper values.

list all field in tabel ISSUE

SELECT ISSUE.[Tag_ no], ISSUE.[Miv_ no], ISSUE.Qty_issue, ISSUE.[TT-73_no], ISSUE.Qty_issue, ISSUE.[TT-73_no], ISSUE.[Rec_ by], ISSUE.[Iso_ no], ISSUE.Double, ISSUE.Tmpe
FROM ISSUE;

这篇关于将交叉表查询转换为sql 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！