SQL Results in Rows not Columns

I am trying to list the results of my query in rows not columns. I have research Pivot Tables and numerous items and thought I would post my question here. This query is getting the list of deposits made for a restaurant chain. There could be 1 per day or up to 5. The manufactures software has them listed in the normal transactions and not numbered in any way.

Below is my query.

use SpeedlinkDB
SELECT 
  ad.StoreID,
  (ad.Amount * -1) as anAmount    
FROM AccountDetail ad
LEFT JOIN DayFileSummary DF ON ad.StoreId = DF.StoreID and ad.EODID = DF.EODID    
WHERE 
(DATEDIFF(d, df.DF_BusinessDay, GETDATE()) = 1)    
AND ( ad.TransType = 18 )
GROUP BY 
  ad.StoreID,
  ad.Amount    
ORDER BY ad.StoreID

Query Results....

StoreID    anAmount
1104       667.28
1104       110.00
1107       750.00
1107       211.00
1107       464.20
1205       280.11
etc..

What I would like is the following...

StoreID    Deposit1    Deposit2    Deposit3    Deposit4
1104       667.28      110.00  
1107       750.00      211.00      464.20
1205       280.11

The main reason being we use Reporting Services to display summary information for the management on the previous day. Among other information they want to see each deposit, sales, etc...

Thank you in adv开发者_JAVA技巧ance for any help that can be provided.

You could use the ROW_NUMBER function to assign sequential numbers for each StoreID and then use the ID and key to do the Pivot.

If you have some other parameter (for eg. deposit time) that you want to order the deposits by, you can add the order by in the partition by windowing clause.

Something like this.

select StoreID,
       max(case DepositNumber when 1 then Deposit else null end) Deposit1
       max(case DepositNumber when 2 then Deposit else null end) Deposit2
       max(case DepositNumber when 3 then Deposit else null end) Deposit3
       max(case DepositNumber when 4 then Deposit else null end) Deposit4
       max(case DepositNumber when 5 then Deposit else null end) Deposit5
  from (
            SELECT StoreID, 
                   Deposit, 
                   ROW_NUMBER() OVER(partition by StoreID) AS DepositNumber
            from (<< Your Query above>> 
        )
   group by StoreID;

Building on Rajesh Chamarthi's answer (+1), you can avoid the case statement and pivot if you use a Matrix Control:

http://www.sqlservercentral.com/articles/Reporting+Services+%28SSRS%29/63415/

You'll still need to use Rajesh's ROW_NUMER() in order to split it out by deposit... but you can skip the case statements if using the Matrix Control...

SELECT 
    StoreID, 
    Deposit, 
    ROW_NUMBER() OVER(partition by StoreID) AS DepositNumber
from (<< Your Query above>>)

Thanks to both of you for your help. The second answer used less resources and was faster but appreciate the input. My mistake was replacing the partition with the order by due to an error I was receiving. Use them both and I get exactly what I am looking for. Thanks!

SELECT 
ad.StoreID, 
ad.Amount, 
ROW_NUMBER() OVER(Partition By ad.StoreID Order by ad.StoreID) AS DepositNumber
FROM AccountDetail ad
LEFT JOIN DayFileSummary DF ON ad.StoreId = DF.StoreID and ad.EODID = DF.EODID

WHERE 
(DATEDIFF(d, df.DF_BusinessDay, GETDATE()) = 1)

AND (ad.TransType = 18)