How to combine two rows into one row with same ID?

Justin Doh

unread,

Oct 22, 2008, 7:47:01 PM10/22/08

to

Hi
I have a data that needs to be arranged from two rows into one row as follow:

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.

Plamen Ratchev

unread,

Oct 22, 2008, 9:32:27 PM10/22/08

to

You can rank by date and then pivot based on the rank. Here is example
(SQL Server 2005/2008):

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

Justin Doh

unread,

Oct 23, 2008, 12:42:00 PM10/23/08

to

Hi 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

Plamen Ratchev

unread,

Oct 23, 2008, 12:52:34 PM10/23/08

to

Here 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

Tom Cooper

unread,

Oct 23, 2008, 1:03:02 PM10/23/08

to

One 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

"Justin Doh" <Just...@discussions.microsoft.com> wrote in message
news:2D11B09D-EDD8-485B...@microsoft.com...

Justin Doh

unread,

Oct 23, 2008, 2:17:01 PM10/23/08

to

Thank you so much!
It was great success.

Appreciated for your help.

kevin adamson

unread,

Jan 31, 2011, 10:46:07 AM1/31/11

to

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.

>> On Wednesday, October 22, 2008 9:32 PM Plamen Ratchev wrote:

>> You can rank by date and then pivot based on the rank. Here is example
>> (SQL Server 2005/2008):
>>
>> 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

>>> On Thursday, October 23, 2008 12:42 PM JustinDo wrote:

>>> Hi 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
>>>
>>> "Plamen Ratchev" wrote:

>>>>> On Thursday, October 23, 2008 1:03 PM Tom Cooper wrote:

>>>>> One 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
>>>>>
>>>>> "Justin Doh" <Just...@discussions.microsoft.com> wrote in message
>>>>> news:2D11B09D-EDD8-485B...@microsoft.com...

>>>>>> On Thursday, October 23, 2008 2:17 PM JustinDo wrote:

>>>>>> Thank you so much!
>>>>>> It was great success.
>>>>>>
>>>>>> Appreciated for your help.
>>>>>>
>>>>>> "Plamen Ratchev" wrote:

>>>>>>> On Tuesday, May 26, 2009 10:42 AM Mohammad Shaik wrote:

>>>>>>> How should I merge two rows with different account number.The above example is similar to mine please help me with it.

>>>>>>>> On Tuesday, May 26, 2009 10:43 AM Muzz wrote:

>>>>>>>> How should I merge two rows with different account number.The above example is similar to mine please help me with it.

>>>>>>>>> On Monday, August 10, 2009 11:31 AM yeehaw mcgraw wrote:

>>>>>>>>> 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.

>>>>>>>>>> On Wednesday, August 12, 2009 3:22 AM kiran wrote:

>>>>>>>>>> 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.

>>>>>>>>>>> On Wednesday, August 12, 2009 3:23 AM kiran wrote:

>>>>>>>>>>> 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

Mohankrishnachowdary Kaka

unread,

Feb 21, 2023, 6:19:29 AM2/21/23

to

hi
i want query based on the data from usercode, username,empname,managername,rolename, by using 3 tables emptable,managertable,roletable by using joins or by using stuff