更新时间:2023-01-29 11:32:07
我们实际上没有足够的信息来转换特定的交叉表查询,因此,以下是一个简单的示例,可以帮助您实现目标:
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://***.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