From: esquel@sommarskog.se   
      
   migurus (migurus@yahoo.com) writes:   
   > I have following input:   
   >   
   > select TIER1, TIER2, AMOUNT   
   > from   RESULT_SET1;   
   >   
   > TIER1 TIER2 AMOUNT   
   >===== ===== ======   
   > 1     1     400   
   > 1     2     150   
   > 1     3     100   
   > 2     1     300   
   > 2     2     20   
   > 2     3     30   
   >   
   > I need to build a matrix out of it, as below:   
   >   
   >   
   >       TIER1   
   >       =====   =====   
   >       1       2   
   > T   
   > I 1 :  40%     30%   
   > E 2 :  15%      2%   
   > R 3 :  10%      3%   
   > 2   
   >   
      
   This is a pivot operation, with some small twists because of the   
   percentage thing. We also need to first compute the total to have   
   a base for the percentage calculations.   
      
   CREATE TABLE #migurus (tier1   int NOT NULL,   
                          tier2   int NOT NULL,   
                          amount  int NOT NULL)   
   go   
   INSERT #migurus (tier1, tier2, amount)   
   VALUES(1,     1,     400),   
   (1,     2,     150),   
   (1,     3,     100),   
   (2,     1,     300),   
   (2,     2,     20),   
   (2,     3,     30)   
   go   
    WITH totals AS (   
       SELECT tier1, tier2, amount,   
              SUM(amount) OVER () AS total   
       FROM   #migurus   
   )   
   SELECT tier2,   
          convert(int, 1E2*SUM(CASE tier1 WHEN 1 THEN amount END)   
             / total) AS [1],   
          convert(int, 1E2*SUM(CASE tier1 WHEN 2 THEN amount END)   
             / total) AS [2]   
   FROM   totals   
   GROUP  BY tier2, total   
   go   
   DROP TABLE #migurus   
      
      
   Note that this is done without the PIVOT operator, which is not very   
   useful.   
      
      
   --   
   Erland Sommarskog, Stockholm, esquel@sommarskog.se   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)