Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1678

Re: how to make a matrix from a series of data

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

how to make a matrix from a series of data migurus <migurus@yahoo.com> - 2014-02-11 09:49 -0800
  Re: how to make a matrix from a series of data Erland Sommarskog <esquel@sommarskog.se> - 2014-02-11 22:07 +0100
  Re: how to make a matrix from a series of data migurus <migurus@yahoo.com> - 2014-02-11 15:21 -0800

csiph-web