Project query results in column

Question

I have a relatively simple join wich extracts data from various tables

SELECT P.StoreName AS Store, CR.Model AS Terminal, T.TxType, COUNT(*) as Total
  FROM [SVTx] SVCT
       INNER JOIN [Txs] T ON T.TransactionID = SVCT.transactionID
       INNER JOIN [TxIss] TI ON TI.TransactionID = T.transactionID
       INNER JOIN [Stores] P ON P.[ExtStoreID] = TI.[ExtStoreID]
       INNER JOIN [Terms] CR ON CR.[StoreID] = P.StoreID AND CR.[ExtTermCode] = TI.[ExtTermCode]
       INNER JOIN [SVCrd] SVC ON SVC.SVCrdID = SVCT.SVCrdID
GROUP BY P.StoreName, CR.Model, TxType
ORDER BY P.StoreName, CR.Model, TxType

and produce a results like the following

Store     Terminal     TransactionType     Total
-------   ----------   -----------------   -----------
Store 1   Terminal 1   1                   23
Store 1   Terminal 1   2                   17
Store 1   Terminal 2   1                   28
Store 1   Terminal 2   2                   24
...       ...          ...                  ..

which gives me, for every store and terminal the number of transactions that have been made by any given type.

I would like to modify my query to add the total transaction made by any given store/terminal and project the results in column instead of rows. To have an idea please have a look at the following example:

Store     Terminal     1        2       Total
-------   ----------   -----    -----   -------
Store 1   Terminal 1   23       17      40
Store 1   Terminal 2   28       24      52

I have read about the PIVOT function but I can only find help for SQL Server 2008 while I am using Azure SQL Database (SQL Server 2014 compatibility level).

Any help?

Answer 1

Use conditional Aggregate instead

;WITH cte 
     AS (SELECT p.storename AS Store, 
                cr.model    AS Terminal, 
                t.txtype, 
                Count(*)    AS Total 
         FROM   [svtx] SVCT
            INNER JOIN [Txs] ...),
     intr 
     AS (SELECT store, 
                terminal, 
                Coalesce(Max(CASE WHEN transactiontype = 1 THEN total END),0)   AS [1], 
                Coalesce(Max(CASE WHEN transactiontype = 2 THEN total END),0)   AS [2], 
                Sum(total) AS total 
         FROM   cte 
         GROUP  BY store, 
                   terminal) 
SELECT store, 
       terminal, 
       [1], 
       [2], 
       total, 
       ( [1] / Cast(total AS NUMERIC(22, 6)) ) * 100 AS percent1, 
       ( [2] / Cast(total AS NUMERIC(22, 6)) ) * 100 AS percent2 
FROM   intr