I have the following code:

DECLARE @BaseQuery TABLE 
                   (
                       PORT_NAME NVARCHAR(50),
                       BILL_ACCOUNT_NAME NVARCHAR(50), 
                       AVERAGE float
                   );

INSERT INTO @BaseQuery 
    SELECT 
        a.[PORT_NAME]PORT_NAME,
        a.[BILL_ACCOUNT_NAME],
        SUM(a.[TOTAL_PRE_VAT_AMNT_PER_INVOICE]) /
            IIF((MAX(a.[YEAR]) - MIN(a.[YEAR])) = 0, 1, (MAX(a.[YEAR]) - MIN(a.[YEAR]))) AVERAGE
    FROM
        (SELECT 
             YEAR(r.[PAYMENT_DATE])YEAR,
             r.[TOTAL_PRE_VAT_AMNT_PER_INVOICE],
             p.[PORT_NAME],
             ISNULL(m.[BILL_ACCOUNT_NAME_AR], N'غير معرف') BILL_ACCOUNT_NAME
         FROM 
             [DD_MART].[dbo].[DM_PORT_REVENUE] r
         LEFT JOIN 
             [DD_MART].[dbo].[DIM_PORT] p ON r.[PORT_ID] = p.[PORT_ID]
         LEFT JOIN 
             [DD_MART].[dbo].[DIM_BILL_MAIN_ACCOUNT] m ON r.[ACCOUNT_CODE] = m.[BILL_ACCOUNT_CODE]) a
  WHERE  
      a.[YEAR] IN ((YEAR(SYSDATETIME())-1),(YEAR(SYSDATETIME())-2),(YEAR(SYSDATETIME())-3)) 
  GROUP BY 
      a.[PORT_NAME], a.[BILL_ACCOUNT_NAME];

DECLARE @Query NVARCHAR(MAX), @Columns NVARCHAR(MAX)

SELECT @Columns =
STRING_AGG(Product, ',')
FROM
(
SELECT DISTINCT
  QUOTENAME(PORT_NAME) AS Product
FROM @BaseQuery
) AS D;

SET @Query =
N'SELECT
BILL_ACCOUNT_NAME, '  @Columns   N'

FROM
(
SELECT 
         a.[PORT_NAME]PORT_NAME
         ,a.[BILL_ACCOUNT_NAME]
         ,SUM(a.[TOTAL_PRE_VAT_AMNT_PER_INVOICE])
         /IIF((MAX(a.[YEAR])-MIN(a.[YEAR]))=0,1,(MAX(a.[YEAR])-MIN(a.[YEAR])))AVERAGE
from (
SELECT YEAR(r.[PAYMENT_DATE])YEAR
      ,r.[TOTAL_PRE_VAT_AMNT_PER_INVOICE]
         ,p.[PORT_NAME]
         ,ISNULL(m.[BILL_ACCOUNT_NAME_AR],N''غير معرف'')BILL_ACCOUNT_NAME
  FROM [DD_MART].[dbo].[DM_PORT_REVENUE] r
  LEFT JOIN [DD_MART].[dbo].[DIM_PORT] p 
  on r.[PORT_ID]=p.[PORT_ID]
  LEFT JOIN [DD_MART].[dbo].[DIM_BILL_MAIN_ACCOUNT] m
  on r.[ACCOUNT_CODE]=m.[BILL_ACCOUNT_CODE]
  )a
  WHERE a.[YEAR] IN ((YEAR(SYSDATETIME())-1),(YEAR(SYSDATETIME())-2),(YEAR(SYSDATETIME())-3)) 
  GROUP BY a.[PORT_NAME]
         ,a.[BILL_ACCOUNT_NAME]
) AS D
PIVOT
(
MAX(AVERAGE) FOR [PORT_NAME] IN ('   @Columns   ')
) AS P'

EXEC sp_executesql @Query;

I need to save the output as a table, I tried to add the following code, since the columns need to be dynamic to schedule a SQL job:

DECLARE @sql varchar(max)
SET @sql = 'CREATE TABLE ##T1 (BILL_ACCOUNT_NAME NVARCHAR(50),'   @Columns   ' float)'
EXEC(@sql)
INSERT INTO ##T1 VALUES (select @Query)
SELECT * FROM ##T1

got (Incorrect syntax near the keyword 'select'.)

I am working in SQL Server Management Studio 18 (2019 version), I tried to read the code in SSIS was not able to read the results.

CodePudding user response：

You can insert your pivot result into a new table object using INTO.

Quick Example:

Executes the @Query string which creates the dbo.RESULT_PIVOT_TABLE object.

SET @Query = 'SELECT
    BILL_ACCOUNT_NAME,
    '   @Columns   N'
INTO dbo.RESULT_PIVOT_TABLE -- INSERT PIVOT RESULT INTO TABLE OBJECT
FROM (SELECT
          PORT_NAME,
          BILL_ACCOUNT_NAME,
          AVERAGE
      FROM #BaseQuery) AS a PIVOT (MAX(AVERAGE) FOR a.PORT_NAME IN ('   @Columns   ')) AS P';

EXEC sp_executesql @Query;

SELECT * FROM dbo.RESULT_PIVOT_TABLE;

Full Example:

DROP TABLE IF EXISTS dbo.RESULT_PIVOT_TABLE;
DROP TABLE IF EXISTS #BaseQuery;

CREATE TABLE #BaseQuery
(
    PORT_NAME         NVARCHAR(50),
    BILL_ACCOUNT_NAME NVARCHAR(50),
    AVERAGE           FLOAT
);
DECLARE @Query NVARCHAR(MAX);
DECLARE @Columns NVARCHAR(MAX);

/* POPULATE TEMP DUMMY TABLE WITH FAKE DATA */
INSERT INTO #BaseQuery
SELECT
    a.PORT_NAME,
    a.BILL_ACCOUNT_NAME,
    a.AVERAGE
FROM (SELECT
          'PORT 1' AS PORT_NAME,
          'NAME 1' AS BILL_ACCOUNT_NAME,
          1.23 AS AVERAGE
      UNION ALL
      SELECT
          'PORT 2' AS PORT_NAME,
          'NAME 2' AS BILL_ACCOUNT_NAME,
          2.34 AS AVERAGE
      UNION ALL
      SELECT
          'PORT 3' AS PORT_NAME,
          'NAME 3' AS BILL_ACCOUNT_NAME,
          3.45 AS AVERAGE) a;

/* SET COLUMNS FOR PIVOT */
SELECT
    @Columns = STRING_AGG(a.PORT_NAME, ',')
FROM (SELECT DISTINCT
          QUOTENAME(PORT_NAME) AS PORT_NAME
      FROM #BaseQuery) a;

/* SET DYNAMIC QUERY STRING THAT INSERTS PIVOT RESULT INTO TABLE OBJECT */
SET @Query = 'SELECT
    BILL_ACCOUNT_NAME,
    '   @Columns   N'
INTO dbo.RESULT_PIVOT_TABLE -- INSERT PIVOT RESULT INTO TABLE OBJECT
FROM (SELECT
          PORT_NAME,
          BILL_ACCOUNT_NAME,
          AVERAGE
      FROM #BaseQuery) AS a PIVOT (MAX(AVERAGE) FOR a.PORT_NAME IN ('   @Columns   ')) AS P';

EXEC sp_executesql @Query;

SELECT * FROM dbo.RESULT_PIVOT_TABLE;

Result: