Convert an Access crosstab query to T-SQL (SQL Server)

Question

I have the following crosstab query in Access:

Transform Count(1) as Count
Select Cust,[Cust#],EntryDate,CloseDate
from Tbl1,Dates
where EntryDate>=[start date]
Group by Cust,[Cust#],EntryDate,CloseDate
Order by EntryDate
Pivot Quote;

I am having difficulty converting this to T-SQL. Should I be using SSIS for Pivot transformation in order to solve this, or do we have an equivalent SQL Server query for this?

Is this a parameter query in Access? In other words, when you run the query in Access does it prompt the user for any values (like [start date], perhaps)? — Gord Thompson, Oct 16 '13 at 20:12
There are no parameters specified in the query as of my knowledge, the main aim is to convert this script to T-Sql but I am not able to understand the logic for that — user2701912, Oct 16 '13 at 20:51
It's proabably easier to show an example of the data in the table and the resultant crosstab data. But crosstabs are bad news because they have dynamic columns, and neither T-SQL or SSIS really like that. — Nick.Mc, Oct 17 '13 at 02:56

Gord Thompson · Answer 1 · 2014-06-10T12:10:49.590

We don't really have enough information to convert that specific crosstab query, so here is a simple example that may help you achieve your goal:

For a table named [Vehicles] containing...

VehicleID  VehicleMake  VehicleModel  VehicleType 
---------  -----------  ------------  ------------
        1  Ford         Focus         Compact car 
        2  Ford         F-150         Pickup truck
        3  Dodge        RAM 1500      Pickup truck
        4  Toyota       Tundra        Pickup truck
        5  Toyota       Prius         Hybrid car  
        6  Toyota       Tacoma        Pickup truck

...the Access crosstab query...

TRANSFORM Count(Vehicles.VehicleID) AS CountOfVehicleID
SELECT Vehicles.VehicleType
FROM Vehicles
GROUP BY Vehicles.VehicleType
PIVOT Vehicles.VehicleMake;

...returns:

VehicleType   Dodge  Ford  Toyota
------------  -----  ----  ------
Compact car             1        
Hybrid car                      1
Pickup truck      1     1       2

The following T-SQL script accomplishes the same thing

DECLARE 
        @ColumnList AS NVARCHAR(MAX),
        @SQL AS NVARCHAR(MAX)

-- build the list of column names based on the current contents of the table
--     e.g., '[Dodge],[Ford],[Toyota]'
--     required by PIVOT ... IN below
--     ref: http://stackoverflow.com/a/14797796/2144390     
SET @ColumnList = 
        STUFF(
            (
                SELECT DISTINCT ',' + QUOTENAME([VehicleMake])
                FROM [Vehicles] 
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 
            1, 
            1, 
            '')
SET @SQL = '
        WITH rollup
        AS
        (
            SELECT VehicleMake, VehicleType, COUNT(VehicleID) AS n FROM [Vehicles]
            GROUP BY VehicleMake, VehicleType
        )
        SELECT * FROM rollup
        PIVOT (SUM([n]) FOR [VehicleMake] IN (' + @ColumnList + ')) AS Results'
EXECUTE(@SQL)

It returns:

VehicleType   Dodge  Ford  Toyota
------------  -----  ----  ------
Compact car    NULL     1    NULL
Hybrid car     NULL  NULL       1
Pickup truck      1     1       2

Please add some comments about the issues with dynamic SQL and the reason for the code you've provided having to be dynamic in the first place. Personally, I would prefer a generic pivoting stored procedure over custom code re-written every time. Can you offer one of those? — ErikE, Oct 18 '13 at 00:38

score 0 · Answer 2 · edited Oct 20 '14 at 21:18

drop table #tmpT1
select distinct UserField2 into #tmpT1 from CreateExcelForm
--select * from #tmpT1
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = 
COALESCE(
@PivotColumnHeaders + ',[' + cast(UserField2 as varchar) + ']',
'[' + cast(UserField2 as varchar)+ ']'
)
FROM #tmpT1
print(@PivotColumnHeaders)
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM (
SELECT
* from CreateExcelForm
) AS PivotData
PIVOT (
max(StockCode)
FOR UserField2 IN (
' + @PivotColumnHeaders + '
)
) AS PivotTable
'

EXECUTE(@PivotTableSQL)

Convert an Access crosstab query to T-SQL (SQL Server)

2 Answers2

Linked

Related