SQL Server 2008 +创建跨表存储过程

Question

我有两个表想要连接并在 SQL 2008 中创建交叉表：

TableA:

 Auto_ID | Fiscal_Period | Amount 
   1     | 01012012      | NULL 
   1     | 01022012      | 80 
   1     | 01032012      | NULL 
   2     | 01012012      | NULL 
   2     | 01022012      | 10 

TABLEB:

Auto_ID | Row_ID | StaticData
   1    |    1   | sampledata
   2    |    2   | data1

我想使用交叉表动态创建以下表结构：

Row_ID | StaticData  | FiscalPeriod(01012012) | FiscalPeriod(01022012) | FiscalPeriod(01032012)
   1   | sampledata  | NULL                   | 80                     | NULL
   2   | data1       | NULL                   | 10                     | NULL

我当前的查询正确连接了表；但是，我很难将会计期间转入我的标题行。

SELECT *
FROM (SELECT 
         B.Row_Id as RowID, B.StaticData as StaticData, A.Fiscal_Period AS FPPD 
      FROM TableA A 
      LEFT JOIN TableB B ON A.Auto_ID = B.Auto_ID)  

Answer 1

这就是我要做的：

首先创建一些测试数据：

CREATE TABLE tblA (Auto_ID INT,Fiscal_Period  VARCHAR(100),Amount FLOAT)
CREATE TABLE tblB (Auto_ID INT,Row_ID INT,StaticData VARCHAR(100))
INSERT INTO tblA
SELECT 1,'01012012',NULL UNION ALL
SELECT 1,'01022012',80 UNION ALL
SELECT 1,'01032012',NULL UNION ALL
SELECT 2,'01012012',NULL UNION ALL
SELECT 2,'01022012',10 

INSERT INTO tblB
SELECT 1,1,'sampledata' UNION ALL
SELECT 2,2,'data1'

然后找到唯一的列：

DECLARE @cols VARCHAR(MAX)
;WITH CTE
AS
(
SELECT
    ROW_Number() OVER(PARTITION BY tblA.Fiscal_Period ORDER BY tblA.Fiscal_Period) AS RowNbr,
    tblA.Fiscal_Period
FROM
    tblA AS tblA
)
SELECT
     @cols = COALESCE(@cols + ','+QUOTENAME('FiscalPeriod('+Fiscal_Period+')'),
                 QUOTENAME('FiscalPeriod('+Fiscal_Period+')'))
FROM
    CTE
WHERE
    CTE.RowNbr=1

然后使用动态 sql 执行数据透视：

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
SELECT
    tblB.Row_ID,
    tblb.StaticData,
    ''FiscalPeriod(''+tblA.Fiscal_Period+'')'' AS Name,
    tblA.Amount
FROM
    tblA AS tblA
    JOIN tblB AS tblB
        ON tblA.Auto_ID=tblB.Auto_ID
) AS p
PIVOT
(
    SUM(Amount)
    FOR Name IN ('+@cols+')
) AS Pvt'
EXECUTE(@query)

然后在我的例子中我将删除临时表：

DROP TABLE tblA
DROP TABLE tblB

我希望这会对您有所帮助

Answer 2

由于您没有指定数据库类型，请注意，以下内容仅适用于 MySQL！

交叉表查询只能通过非常肮脏的技巧才能实现，它实际上只在存储过程中可行。

您首先想出某种方法，将会计周期列表转换为 SQL，例如

SELECT
  TABLEB.Row_ID,
  TABLEB.staticdata
  ,fp01012012.Amount as fp01012012amount
  ,fp01022012.Amount as fp01022012amount
  ,fp01032012.Amount as fp01032012amount
FROM
  TABLEB
  LEFT JOIN TableA AS fp01012012 ON fp01012012.Auto_ID=TABLEB.Auto_ID AND fp01012012.Fiscal_Period='01012012'
  LEFT JOIN TableA AS fp01022012 ON fp01022012.Auto_ID=TABLEB.Auto_ID AND fp01022012.Fiscal_Period='01022012'
  LEFT JOIN TableA AS fp01032012 ON fp01032012.Auto_ID=TABLEB.Auto_ID AND fp01032012.Fiscal_Period='01032012'

您现在必须将其构建为动态 SQL - 这仅在存储过程中可行。

DELIMITER $

DROP PROCEDURE IF EXISTS `create_fiscal_data`$
CREATE PROCEDURE `create_fiscal_data` ()
BEGIN
      DECLARE dynfields VARCHAR(10000) DEFAULT 'SELECT TABLEB.Row_ID, TABLEB.staticdata';
      DECLARE dynfrom VARCHAR(10000) DEFAULT ' FROM TABLEB';
      DECLARE period VARCHAR(10) DEFAULT '';
      DECLARE done INT DEFAULT 0;
      DECLARE id INT DEFAULT 7;
      DECLARE periods CURSOR FOR SELECT DISTINCT Fiscal_Period FROM TableA;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
      OPEN periods;

      cycleperiods: LOOP
          FETCH periods INTO period;
          IF done=1 THEN LEAVE cycleperiods; END IF;

          SET dynfields=CONCAT(dynfields,',`fp',period,'`.Amount AS `fp',period,'amount`');
          SET dynfrom=CONCAT(dynfrom,' LEFT JOIN TableA AS `fp',period,'` ON `fp',period,'`.Auto_ID=TABLEB.Auto_ID AND `fp',period,'`.Fiscal_Period="',period,'"');

      END LOOP;

      CLOSE periods;

      SELECT @dynsql:=CONCAT(dynfields,dynfrom) INTO dynfields;
      -- Here comes the trick!
      PREPARE dynqry FROM @dynsql;
      EXECUTE dynqry;

END$

DELIMITER ;

诀窍是，将 SQL 构建到变量 @dynsql 中（DECLARE d 变量不起作用），然后准备并执行它。

现在查询

CALL `create_fiscal_data;`

将创建您需要的输出。