How to use PIVOT in SQL for this sample

Question

How do I pivot a results query?

Currently it looks like this

|   Date  |  Count   | BankName |
+---------+----------+----------+
|  970401 |    87    |  Saderat |
|  970401 |    25    |  Melli   |
|  970401 |    11    |   Sina   |

into this

|Date     | Saderat  |   Melli  |   Sina   |
+---------+----------+----------+----------+
|970401   | 87       |   25     |   11     |

I tried the following but it's not working

SELECT  
    PayDate AS [Date], 
    COUNT(*) AS [Count], b.BankName 
FROM   
    Payments p 
INNER JOIN  
    dbo.Accounts a ON a.AccountId = p.CashAccountId
INNER JOIN  
    dbo.Banks b ON b.BankId = a.BankId
WHERE   
    PayTypeId = 21.101 
    AND PayDate BETWEEN '970401' AND '970412' 
GROUP BY 
    PayDate, b.BankName
ORDER BY 
    paydate

or

SELECT 
    x.PayDate AS 'Date',
    b.BankName 
FROM 
    (SELECT 
         p.PayDate, p.PaymentId, p.CashAccountId 
     FROM   
         Payments p 
     WHERE   
         PayTypeId = 21.101 
         AND PayDate BETWEEN '970401' AND '970412') AS x  
INNER JOIN  
    dbo.Accounts a ON a.AccountId = x.CashAccountId 
INNER JOIN  
    dbo.Banks b ON b.BankId = a.BankId
PIVOT
    (COUNT(PaymentId) FOR PayDate IN (bankid)) AS Pivotable

Can you explain your desired result field names? Or, more importantly: Why does 87 go to the first and 42 to the second? — George Menoutis, Jul 03 '18 at 10:38
You might want to post only in English, as most folks can't really type out Arabic text on their keyboards. After that, just Google for "SQL pivot query." SQL Server also has a `PIVOT` operator would you could use. — Tim Biegeleisen, Jul 03 '18 at 10:39
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Chris Pickford, Jul 03 '18 at 10:55

LukStorms · Answer 1 · 2018-07-04T22:32:04.087

You could PIVOT

SELECT *
FROM
(
  SELECT 
   p.PayDate AS [Date], 
   b.BankName 
  FROM dbo.Payments p 
  JOIN dbo.Accounts a ON a.AccountId = p.CashAccountId
  JOIN dbo.Banks b ON b.BankId = a.BankId
  WHERE p.PayTypeId = 21.101 
    AND p.PayDate BETWEEN CAST('1997-04-01' AS DATE) AND CAST('1997-04-12' AS DATE)
) src
PIVOT 
(
  COUNT(*)
  FOR BankName IN (...) -- put quoted list of bank names here 
) pvt
ORDER BY [Date]

score 1 · Answer 2 · answered Jul 03 '18 at 10:59

You can try following SQL for required results:

SELECT PayDate, Saderat, Melli, Sina
FROM
(SELECT  PayDate  , COUNT(*) AS [Count]  , b.BankName 
FROM   Payments p INNER JOIN  dbo.Accounts a  ON a.AccountId = p.CashAccountId
INNER JOIN  dbo.Banks b ON b.BankId = a.BankId
WHERE   PayTypeId = 21.101 AND PayDate BETWEEN '970401' AND '970412' 
GROUP BY PayDate , b.BankName
ORDER BY paydate) AS SourceTable
PIVOT
(
 SUM([Count])
 FOR BankName IN (Saderat, Melli, Sina)
) AS PivotTable;

score 1 · Answer 3 · answered Jul 03 '18 at 14:50

You can do aggregation :

SELECT PayDate AS [Date],
       SUM(CASE WHEN b.BankName = 'Saderat' THEN 1 ELSE 0 END) AS Saderat,
       . . .  
FROM Payments p INNER JOIN  
     dbo.Accounts a 
     ON a.AccountId = p.CashAccountId INNER JOIN  
     dbo.Banks b 
     ON b.BankId = a.BankId
WHERE PayTypeId = 21.101 AND PayDate BETWEEN '970401' AND '970412' 
GROUP BY PayDate
ORDER BY paydate;

score 1 · Answer 4 · answered Jul 03 '18 at 14:51

1

You can use PIVOT function in SQL Server, try following query:

SELECT date, [Saderat],[Melli],[Sina]
FROM YourTableName
PIVOT( MAX(count) 
FOR BankName IN ([Saderat],[Melli],[Sina])) AS p

answered Jul 03 '18 at 14:51

Aura

1,283
2
16
30

How to use PIVOT in SQL for this sample

4 Answers4