I have following table and finding difficult to produce expected output. I have tried following query but it doesn't produce correct 'AVG' using 'PIVOT'. The average value should show in respective columns of category as per the output. In my below query it doesn't work

SELECT *
FROM (select     avg(hd.SCORE) over(partition by hd.org_id) Avg_Item
, hd.org_id,d.cateogory,hd.score FROM Category d JOIN Assign hd ON d.catid=hd.catid) first
PIVOT (
  AVG(score)
  FOR (cateogory)
  IN ('cat1' as "cat1",'cat2' as "cat2",'cat3' as "cat3",'cat4' as "cat4")
)PIV;

Sample Tables & Expected Output

Category table                     
-----------------------
catid  cateogory              

1      cat1
2      cat2
3      cat3
4      cat4
5      cat5


Assign table
--------------------------
Aid(pk)  catid(fk) pid Score org_id
1          1       1     98  1
2          1       2     99  1
3          1       3     100 1
4          2       4      12 4
5          2       8       78 5
6          5       9       98 6


org Table
-----------------------------
org_id org_name
1        ABC
2        CDE
3        FGH
4        Google
5        Yahoo
6        Facebook


Desired output
----------------------------------

org_name   cat1  cat2  cat3  cat4 cat5
 
ABC         99 
CDE               12     
FGH              
Google            78
Yahoo                              98
Facebook

CodePudding user response：

Assuming score is actually a number, not a string like 98%, then your current query seems to work, and (with cat5 added) produces:

which seems right.

But you aren't using the analytic avg_item you calculate in your desired result, so you can remove that form the subquery. And you can outer join the result of the pivot to the org table to get the organisation names:

SELECT o.org_name, p.cat1, p.cat2, p.cat3, p.cat4, p.cat5
FROM org o
LEFT JOIN (
  SELECT *
  FROM (
    SELECT hd.org_id, d.cateogory, hd.score
    FROM Category d
    JOIN Assign hd ON d.catid=hd.catid
  )
  PIVOT (
    AVG(score)
    FOR (cateogory)
    IN ('cat1' as cat1, 'cat2' as cat2, 'cat3' as cat3, 'cat4' as cat4, 'cat5' as cat5)
  )
) p
ON p.org_id = o.org_id
ORDER BY o.org_id;

or more simply:

SELECT o.org_name, p.cat1, p.cat2, p.cat3, p.cat4, p.cat5
FROM (
  SELECT hd.org_id, d.cateogory, hd.score
  FROM Category d
  JOIN Assign hd ON d.catid=hd.catid
)
PIVOT (
  AVG(score)
  FOR (cateogory)
  IN ('cat1' as cat1, 'cat2' as cat2, 'cat3' as cat3, 'cat4' as cat4, 'cat5' as cat5)
) p
RIGHT JOIN org o
ON o.org_id = p.org_id
ORDER BY o.org_id;

which both get:

fiddle

Your expected result seems to have the wrong values against the organisations (or the wrong IDs in the org table), but other than that, this seems to be your desired result.