Portland Access User Group

Portland Access User Group Conference September 28-30, 2019

The PAUG Database Designer International conference brings together a wide range of Access developers, consultants, power users and Access enthusiasts. This marks the 21st anniversary of the conference. We will once again be returning to the peaceful and natural surroundings of the Conference Center at Silver Falls State Park, which lends itself to a climate that fosters learning, creativity, and socializing.

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