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

Groups > comp.databases.ms-sqlserver > #1676 > unrolled thread

how to make a matrix from a series of data

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1676 — how to make a matrix from a series of data

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

I can't wrap my brain around making a matrix out of series of values I get from my result set. Any ideas would by much appreciated. We run SQL Server 2008.

[toc] | [next] | [standalone]

#1678

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

[toc] | [prev] | [next] | [standalone]

#1681

On Tuesday, February 11, 2014 9:49:19 AM UTC-8, migurus wrote:
> 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
> 
> 
> 
> I can't wrap my brain around making a matrix out of series of values I get from my result set. Any ideas would by much appreciated. We run SQL Server 2008.

Thank You!

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web