Search code examples
sql-serveraggregateunpivot

Unpivot on aggregated columns

I'm trying for 3 days now to format a sql output so that i can put it in an excel sheet with PHPExcel with the less steps as possible (because i have a very huge Excel sheet to generate)

I am using SQL SERVER 2005

This is what I get Without using any pivot (Because i don't how to do it ..)

- [cause]                  |[nb]| [Value]

- CASSE                    | 20 |  8568,74
- DELEGATION SIGNATURE     |  2 |  1268,96
- ERREUR DE LIVRAISON      |  8 |  1654,64
- INTERNE                  | 10 |  3145,7
- PERTE                    | 94 | 26255,08
- SPOLIATION               |  2 |   420
- VOL                      | 41 |  5747,76

SO now what i want to get is somethng like this :

- [cause]                |  [Value]

- CASSE                  |   20
- CASSE                  | 8568,74
- DELEGATION SIGNATURE   |    2
- DELEGATION SIGNATURE   | 1268,96
- ...

Here is what I've done :

 SELECT 
        l1.Litige_Cause AS [cause], count(l2.Litige_Ref) AS [nb], 
      sum(lc.LitigeCProduit_Valeur) as [value]
    FROM 
       LitigeCordon l1 
     LEFT JOIN LitigeCordon l2 
       ON l1.Litige_Ref = l2.Litige_Ref
       AND YEAR(l2.Litige_DateEnvoi) = 2014 AND MONTH(l2.Litige_DateEnvoi) = 01
       INNER JOIN LitigeCProduit lc
       ON l1.Litige_Ref = lc.Litige_Ref
       AND YEAR(l2.Litige_DateEnvoi) = 2014 AND MONTH(l2.Litige_DateEnvoi) = 01         GROUP BY l1.Litige_Cause

Well, it must admit i'm not really good at SQL, i'm still a student, and what i learned at school are barely the basics. SO my question : is it even possible ? If yes, could you give me an advice, because i'm not searching only for a raw answer, i would like to understand more how the pivot / Unpivot functions are working, i already tried many things but it never worked like a wanted to ...

Thanks !

Edit : For those who might be interrested, I made some changes to @NoDisplayName answer, so that the result looks like this :

- [cause]                Type |  [Value]

- CASSE                    NB |   20
- CASSE                  Value| 8568,74
- DELEGATION SIGNATURE     NB |    2
- DELEGATION SIGNATURE   Value| 1268,96
- ...

I only added the column "type" and unpivoted data for "type" instead of "col"

  SELECT cause, type,
   data as Value
  FROM  (SELECT cause,
            CONVERT(VARCHAR(50), nb) nb1,
            CONVERT(VARCHAR(50), value) value1
         FROM   Yourtable) a
  UNPIVOT(data
            FOR type IN([nb1],
                       [value1]))unpiv

Hopes this helps too :)

Solution

  • I will do this using Cross Apply simple way to Unpivot your data

    SELECT cause,
       cs.value
FROM   yourtable
       CROSS apply(VALUES (value),
                          (CONVERT(VARCHAR(50), nb))) cs (value)

    Or you can also use Unpivot 

    SELECT cause,
       data as Value
FROM  (SELECT cause,
                CONVERT(VARCHAR(50), nb) nb1,
                value
        FROM   Yourtable) a
        UNPIVOT(data
                FOR col IN([nb1],
                           [value]))unpiv

    FIDDLE DEMO