TSQL help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

TSQL help

Author

Topic

sql_learner123
Starting Member

2 Posts

Posted - 2010-12-30 : 23:06:07

Hi Friends,

I am stuck with a query and need direction where to go. I have below data in table called TRANSACTIONS:

 
Clientid   Act#    Deposit   TransactionDate
   100     A-123    $1000        12/1/2010
   100     A-123    $1500        12/5/2010
   100     A-123    $2000        12/8/2010
   100     A-123    $3000        11/25/2010
   200     B-111    $5000        12/20/2010

I want to display like below:


Clientid    Act#     Deposit3  Deposit2  Deposit1
  100      A-123      $2000     $1500     $1000 
  200      B-111      $5000         -           -

Basically it should pivot and show latest 3 Deposits (Deposit3 being the latest) Same clientid can have multiple accounts. And this table is huge.

Please suggest the best possible way because I have to join this data with CLIENT_DETAILS table which has client's name, lastname, age and other personal details.

Thanks in advance.

matty
Posting Yak Master

161 Posts

Posted - 2010-12-31 : 00:10:42

[code]
SELECT Clientid,Act,
max(CASE rownum WHEN 1 THEN Deposit END) AS deposit3,
max(CASE rownum WHEN 2 THEN Deposit END) AS deposit2,
max(CASE rownum WHEN 3 THEN Deposit END) AS deposit1
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Clientid,Act ORDER BY TransactionDate DESC) AS rownum
FROM Transactions
)t
WHERE rownum <=3
GROUP BY Clientid,act
[/code]

sql_learner123
Starting Member

2 Posts

Posted - 2010-12-31 : 00:46:26

Thank you so very much, this worked

matty
Posting Yak Master

161 Posts

Posted - 2010-12-31 : 01:12:36

You are welcome :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources