将 Access 交叉表查询转换为 T-SQL (SQL Server)

问题描述

我在 Access 中有以下交叉表查询:

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;

我很难将其转换为 T-SQL.我是否应该使用 SSIS 进行 Pivot 转换来解决这个问题，或者我们有一个等效的 SQL Server 查询吗?

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?

推荐答案

我们确实没有足够的信息来转换特定的交叉表查询，因此这里有一个简单的示例，可以帮助您实现目标:

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:

对于名为 [Vehicles] 的表，包含...

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

...Access 交叉表查询...

...the Access crosstab query...

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

...返回:

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

下面的 T-SQL 脚本完成同样的事情

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)

它返回:

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

这篇关于将 Access 交叉表查询转换为 T-SQL (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！