December 30, 2010 at 8:25 pm
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.
December 30, 2010 at 9:33 pm
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
December 30, 2010 at 9:47 pm
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
December 30, 2010 at 10:47 pm
Thank you so very much guys, this worked 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply