SQL Join, Aggregate with Pivot

Question

我正在尝试从库存系统创建一个自定义报告。不幸的是，在系统UI中，只有一个选项可以对自定义报告使用查询。我想生成带有资产状态的资产报告，例如:故障、新在库、已用。

SELECT  productType.COMPONENTTYPENAME AS "Product Type", "state"."displaystate" AS "Asset State", count("resource"."resourcename" ) AS "Asset Count" FROM Resources resource
left JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN "resourcestate" "state" ON "resource"."resourcestateid" = "state"."resourcestateid"
LEFT JOIN "resourcetype" "rtype" ON "productType"."resourcetypeid" = "rtype"."resourcetypeid"
GROUP BY  state.displaystate,productType.COMPONENTTYPENAME

使用这个查询，我将获得这种格式的数据

New - In Store

下面的查询解决了这个问题。欢迎提出改进意见查询。

SELECT  "Product Type", "In Use", "Used - In Store", "In Store", "New - In Store", "Damaged","Faulty" FROM (
SELECT  productType.COMPONENTTYPENAME AS "Product Type", "state"."displaystate" AS "Asset State", count("resource"."resourcename" ) AS "Asset Count" FROM Resources resource
left JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN "resourcestate" "state" ON "resource"."resourcestateid" = "state"."resourcestateid"
LEFT JOIN "resourcetype" "rtype" ON "productType"."resourcetypeid" = "rtype"."resourcetypeid"
GROUP BY  state.displaystate,productType.COMPONENTTYPENAME )d
pivot
(
max("Asset Count")
for "Asset State" in ("In Use", "Used - In Store", "In Store", "New - In Store", "Damaged", "Faulty")
) piv

您必须将查询的第一部分命名为CTE，因此它将是这样的:

;with Resources as (
SELECT productType.COMPONENTTYPENAME AS [Product Type],
[state].[displaystate] AS [Asset State],
count([resource].[resourcename] ) AS [Asset Count]
FROM Resources resource
left JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN [resourcestate] [state] ON [resource].[resourcestateid] = [state].[resourcestateid]
LEFT JOIN [resourcetype] [rtype] ON [productType].[resourcetypeid] = [rtype].[resourcetypeid]
GROUP BY  state.displaystate,productType.COMPONENTTYPENAME
)
SELECT [Asset State], [In Use], [Used - In Store], [In Store], [New - In Store], [Damaged], [Faulty]
FROM Resources resource
PIVOT(
sum([Asset Count]) for [Asset State] in ([In Use], [Used - In Store], [In Store], [New - In Store], [Damaged], [Faulty])
)