Monday, August 1, 2016

Access_Crosstab_Query_to_TSQL


Converting an Access Crosstab query into SQL Server (T-SQL) is more difficult than you might think.  Take the following crosstab:

----------------------------------------------------------------------------------------
------  Access Crosstab Query   -----------------
----------------------------------------------------------------------------------------

/*
TRANSFORM Sum(Line_Days) AS SumOfLine_Days
SELECT Factory, Line, Sum(Line_Days) AS [Total Of Line_Days]
FROM Factory_Line_temp
GROUP BY Factory, Line
PIVOT DateID;

*/
 


In order do this in SQL Server, you need to build the SQL statement dynamically, adding the crosstab headers by reading them from the query, then building them in code. 



----------------------------------------------------------------------------------------
------ T-SQL Equivalent -----------------
----------------------------------------------------------------------------------------
DECLARE  @ColumnOrder  AS  TABLE(ColumnName 
                                 varchar(8)
                                 NOT NULL
                                 PRIMARY KEY)


DECLARE  @strSQL  AS NVARCHAR(4000)
INSERT INTO @ColumnOrder SELECT DISTINCT DateID FROM Factory_Line_temp


DECLARE  @XTabColumnNames  AS NVARCHAR(MAX)
DECLARE         @XTabColumn      AS varchar(20)
SET @XTabColumn = (SELECT MIN(ColumnName) FROM  @ColumnOrder)


SET @XTabColumnNames = N''


-- Create the xTab columns


WHILE (@XTabColumn IS NOT NULL)
  BEGIN
    SET @XTabColumnNames = @XTabColumnNames + N',' +
      QUOTENAME(CAST(@XTabColumn AS NVARCHAR(10)))
   SET @XTabColumn = (SELECT MIN(ColumnName)
                          FROM   @ColumnOrder
                          WHERE  ColumnName > @XTabColumn)
  END


SET @XTabColumnNames = SUBSTRING(@XTabColumnNames,2,LEN(@XTabColumnNames))


PRINT @XTabColumnNames


SET @strSQL = N'SELECT * FROM (
              SELECT Factory, Line, DateID, Line_Days
              FROM Factory_Line_temp) as header
              pivot (sum(Line_Days)
                    FOR DateID IN(' + @XTabColumnNames + N'))  AS Pvt '


PRINT @strSQL 


-- Execute strSQL sql

EXEC sp_executesql @strSQL