I am trying to create a custom report from an inventory system. Unfortunately in the system UI, there is only an option to use the query for custom reports. I want to generate the asset report with the asset state e.g. Faulty, New - In-Store, Used.

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

with this query, i am getting the data in this format

I want to convert this Asset state in rows into columns like (Excel Pivot Table) to get the summary.

I tried to change the query to change the result to this format.

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")
)

but this query is not returning anything. Kindly refer to any link or video to understand and fix this issue. Thanks in advance.

CodePudding user response：

The below query solved the issue. Suggestions are most welcome to improve the query.

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

CodePudding user response：

You had to name the first part of the query as a CTE, so it would be like:

;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])
)