CODEWITHSUNDEEP
sqlsql-server
A.Bahrami
A.Bahrami

Reputation: 81

How to use PIVOT in SQL for this sample

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

Upvotes: 1

Views: 96

Answers (4)

LukStorms
LukStorms

Reputation: 29667

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]

Upvotes: 1

Aura
Aura

Reputation: 1307

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 1

Alankrit Agrawal
Alankrit Agrawal

Reputation: 109

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;

Upvotes: 1

Related Questions