pivot in MS SQL query output

Question

I try this query from this query i get result with multiple records with same name

    SELECT 
       S.Fruit
       ,S.RegNo
       ,SUM(total) total 
    FROM (SELECT  
              RV.Fruit
              ,RV.RegNo
              ,COUNT(vv.Fruit) as total
              , RV.MS 
          from vv inner join RV on vv.MID= RV.ID 
          inner join Re on RV.RID=Re.RID 
          WHERE Reg.SDate>='2016-04-0100:00:00.000' and 
                Reg.EDate<= '2016-04-30 23:59:59.000'  and 
                Reg.Reg= 'UK' and RV.RegNo ='375' AND 
                Fruit <> '' 
          GROUP BY  RV.Fruit,RV.RegNo) S 
    GROUP BY S.Fruit,S.RegNo order by S.Fruit

Output:

    Name    RegNo   total    Fruit
   John     375       2     Apples
   John    375        1     Oranges
   John    375        10    Grapes
   John    375        2     Pear
   John    375        14    Mango

where as i want this output

Name    RegNo   Apples Oranges   Grapes   Pear Mango    total   
John    375       2     1          10        2   14        29

so how to get this result?

I think [this](http://stackoverflow.com/questions/12808189/setting-column-values-as-column-names-in-the-sql-query-result) could help. You need to use CASEs — Valentin Sky, Sep 06 '16 at 07:30

score 0 · Accepted Answer · answered Sep 06 '16 at 07:29

0

You can omit Fruit field from the GROUP BY clause and use conditional aggregation:

SELECT S.RegNo,
       SUM(total) AS total,
       SUM(CASE WHEN s.Fruit = 'Apples' THEN total ELSE 0 END) AS Apples,
       SUM(CASE WHEN s.Fruit = 'Oranges' THEN total ELSE 0 END) AS Oranges,
       SUM(CASE WHEN s.Fruit = 'Grapes' THEN total ELSE 0 END) AS Grapes,
       SUM(CASE WHEN s.Fruit = 'Pear' THEN total ELSE 0 END) AS Pear,
       SUM(CASE WHEN s.Fruit = 'Mango' THEN total ELSE 0 END) AS Mango     
FROM (
   SELECT  RV.Fruit,RV.RegNo,COUNT(vv.Fruit) as total, RV.MS 
   from vv 
   inner join RV on vv.MID= RV.ID 
   inner join Re on RV.RID=Re.RID 
   WHERE Reg.SDate>='2016-04-0100:00:00.000' and 
         Reg.EDate<= '2016-04-30 23:59:59.000' and 
         Reg.Reg= 'UK' and RV.RegNo ='375' AND 
         Fruit <> '' 
   GROUP BY RV.Fruit,RV.RegNo) S 
GROUP BY S.RegNo

answered Sep 06 '16 at 07:29

Giorgos Betsos

71,379
9
63
98

i dont want to use condition case.. any other solution – user6628729 Sep 06 '16 at 07:31
1

Cases are a great solution and can perform better then `PIVOT` – sagi Sep 06 '16 at 07:34
how @sagi? i heard that for this condition we have to use pivot – user6628729 Sep 06 '16 at 07:56
@user6628729 Actually `PIVOT` is just syntactic sugar for conditional aggregation. I don't think it makes any difference performance-wise to choose either one or the other. Just pick the method you feel more comfortable with. – Giorgos Betsos Sep 06 '16 at 07:58
Then you probably heard it from someone that didn't hear about conditional aggregation. It's an alternative, and a pretty good one for limited amount of columns. – sagi Sep 06 '16 at 07:59

score 0 · Answer 2 · answered Sep 06 '16 at 07:41

You can use PIVOT:

SELECT  *,
        [Apples]+[Oranges]+[Grapes]+[Pear]+[Mango] as total
FROM (
    SELECT 
       S.Fruit
       ,S.RegNo
       ,SUM(total) total 
    FROM (SELECT  
              RV.Fruit
              ,RV.RegNo
              ,COUNT(vv.Fruit) as total
              , RV.MS 
          from vv inner join RV on vv.MID= RV.ID 
          inner join Re on RV.RID=Re.RID 
          WHERE Reg.SDate>='2016-04-0100:00:00.000' and 
                Reg.EDate<= '2016-04-30 23:59:59.000'  and 
                Reg.Reg= 'UK' and RV.RegNo ='375' AND 
                Fruit <> '' 
          GROUP BY  RV.Fruit,RV.RegNo) S 
    GROUP BY S.Fruit,S.RegNo order by S.Fruit
    ) t
PIVOT (
    MAX(total) FOR Fruit IN ([Apples],[Oranges],[Grapes],[Pear],[Mango])
) pvt

If there are many fruits better use dynamic SQL.

pivot in MS SQL query output

2 Answers2