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
