Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: how to make a matrix from a series of data
Date: Tue, 11 Feb 2014 22:07:12 +0100
Organization: Erland Sommarskog
Lines: 66
Message-ID: <XnsA2D1E1046DA85Yazorman@127.0.0.1>
References: <8cfdc69b-ff92-404a-ae34-d36fe4aedc8c@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx05.eternal-september.org; posting-host="fd3d6d0229f14a752f017d8f9903addd"; logging-data="999"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18inIogrnNbF7ITfljGJKo0"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:lJbMhRTjYx0axGOELEqyKgSn4r8=
Xref: csiph.com comp.databases.ms-sqlserver: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