Similar to the above. But what if you don't know how many rows you will have from one table. I have 2 tables - and sometimes one of the tables has 2 rows and sometimes it has 5. But I still want to combine it all on 1 row. How could I modify the above query if I needed an unknow number of rows to go on the same row (up to 5 but could be none also)? Any help would be great.
Post by Justin DohHi
From two rows into -->
-----------------------------------------
AcctNo : Value 1 : Date 1
-----------------------------------------
1234 : 10,000 : 1/1/08
-----------------------------------------
1234 : 25,000 : 10/1/08
-----------------------------------------
One row -->
-------------------------------------------------------------------------
AcctNo : Value 1 : Date 1 : Value 2 : Date 2
-------------------------------------------------------------------------
1234 : 10,000 : 1/1/08 : 25,000 : 10/1/08
-------------------------------------------------------------------------
I would appreciate for any feedback.
Thanks.
Post by Plamen RatchevYou can rank by date and then pivot based on the rank. Here is example
CREATE TABLE AccountTransactions (
acc_nbr INT,
value DECIMAL(10, 2),
transaction_date DATETIME,
PRIMARY KEY (acc_nbr, transaction_date));
INSERT INTO AccountTransactions VALUES (1234, 10000, '20080101');
INSERT INTO AccountTransactions VALUES (1234, 25000, '20081001');
SELECT acc_nbr,
MAX(CASE WHEN rk = 1 THEN value END) AS value1,
MAX(CASE WHEN rk = 1 THEN transaction_date END) AS date1,
MAX(CASE WHEN rk = 2 THEN value END) AS value2,
MAX(CASE WHEN rk = 2 THEN transaction_date END) AS date2
FROM (SELECT acc_nbr, value, transaction_date,
ROW_NUMBER() OVER(PARTITION BY acc_nbr
ORDER BY transaction_date) AS rk
FROM AccountTransactions) AS A
GROUP BY acc_nbr;
--
Plamen Ratchev
http://www.SQLStudio.com
Post by Justin DohHi Plamen,
Thank you very much for the feedback.
I am afraid that my sp is SQL 2000 version.
Do you have suggestion how the SQL 2000 version's work?
I would appreciate if you let me know.
Thanks.
Justin
Post by Plamen RatchevHere is how you can simulate the same on SQL Server 2000. But note
performance will not be great with large data set.
SELECT acc_nbr,
MAX(CASE WHEN rk = 1 THEN value END) AS value1,
MAX(CASE WHEN rk = 1 THEN transaction_date END) AS date1,
MAX(CASE WHEN rk = 2 THEN value END) AS value2,
MAX(CASE WHEN rk = 2 THEN transaction_date END) AS date2
FROM (SELECT acc_nbr, value, transaction_date,
(SELECT COUNT(*)
FROM AccountTransactions AS B
WHERE B.acc_nbr = A.acc_nbr
AND B.transaction_date <= A.transaction_date) AS rk
FROM AccountTransactions AS A) AS A
GROUP BY acc_nbr;
--
Plamen Ratchev
http://www.SQLStudio.com
Post by Tom CooperOne way (assuming that each acc_nbr has exactly two rows)
SELECT a1.acc_nbr,
a1.value AS value1,
a1.transaction_date AS value2,
a2.value AS value2,
a2.transaction_date AS value2
FROM AccountTransactions a1
INNER JOIN AccountTransactions a2 ON a1.acc_nbr = a2.acc_nbr
AND a1.transaction_date < a2.transaction_date;
Tom
Post by Justin DohThank you so much!
It was great success.
Appreciated for your help.
How should I merge two rows with different account number.The above example is similar to mine please help me with it.
How should I merge two rows with different account number.The above example is similar to mine please help me with it.
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
Submitted via EggHeadCafe
WCF Generic DataContract object Serializer
http://www.eggheadcafe.com/tutorials/aspnet/59ae2b9e-a3be-4cd5-a0ef-939a7abbdc3a/wcf-generic-datacontract-object-serializer.aspx