Need help with query

Question

Post reply

Need help with query

sql_learner123

SSC Rookie

Points: 29
More actions
December 30, 2010 at 8:25 pm

#231523

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Abhijeet Chavan Default port Points: 1491 More actions · Answer 1

Here is the DDL Script in case if any body wants it.

declare @tab table(ClientID int, Act varchar(6), Deposit varchar(6), TransactionDate DateTime)



insert into @tab values (100, 'A-123', '$1000', '12/1/2010')

insert into @tab values (100, 'A-123', '$1500', '12/5/2010')

insert into @tab values (100, 'A-123', '$2000', '12/8/2010')

insert into @tab values (100, 'A-123', '$3000', '11/25/2010')

insert into @tab values (200, 'B-111', '$5000', '12/20/2010')

select * from @tab

WayneS SSC Guru Points: 95405 More actions · Answer 2

Thanks for the DDL script and sample data - makes it worthwhile helping someone if they help us a wee bit first.

How does this work for you?

;



WITH CTE1 AS

(

-- Assign a row number by descending TransactionDate, restarting at each ClientID

SELECT ClientID, Act, Deposit, TransactionDate,

       RN = ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY TransactionDate DESC)

  FROM @tab

), CTE2 AS

(

-- Renumber the top 3 so that if < 3, will fall into the proper bucket

SELECT ClientID, Act, Deposit, TransactionDate,

       RN = ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY RN)

  FROM CTE1

 WHERE RN <= 3

)

SELECT ClientID, Act,

       Deposit3 = MAX(CASE WHEN RN = 1 THEN Deposit ELSE NULL END),

       Deposit2 = MAX(CASE WHEN RN = 2 THEN Deposit ELSE NULL END),

       Deposit1 = MAX(CASE WHEN RN = 3 THEN Deposit ELSE NULL END)

  FROM CTE2

 GROUP BY ClientID, Act

ORDER BY ClientID;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sql_learner123 SSC Rookie Points: 29 More actions · Answer 3

sql_learner123

SSC Rookie

Points: 29

December 30, 2010 at 10:47 pm

#1268011

Thank you so very much guys, this worked 🙂