how to combine two records into one while displaying

here is the table structure
name |account | balance
jessica 234 45.00
mary 234 45.00
Robert 111 50.00
I need show all the persons names having similar account Id in one record
my expected output when i query this should be
jessica 234 45.00
&
mary
Robert 111 50.00
thanks for your reply in advance.Your table design appears to be denormalised. I would have expected Balance
to be in a separate table. Surely the same account number can't have two
different Balances? Fixing this should make your reporting task easier.
If you have a maximum of two names per account then just use MIN and MAX:
SELECT MIN(name),
CASE WHEN MAX(name)>MIN(name)
THEN MAX(name) END,
account, balance
FROM AccountNames
GROUP BY account, balance
If you have an undefined number of names per account then probably the best
approach is to compile your report-style output in your client application
or report writer.
--
David Portas
--
Please reply only to the newsgroup
--|||David,
Thanks for the reply. Altough your right It did not really answer my question. Please forgot about the table structure, main thing when i get an import from other system my table which i showed gets accurate(same) balances for the accounts having same id.
My question is .. when you query the table i need to get the records with the same account id as one record with all the names merged in the name column.
What I was doing is , I was using a cursor to loop through the table and get the names merged . My problem is once I complete the looping process based on accountid I have to insert the record into temporary table . I was wondering is there a way without using this cursors and temp tables to just get the data easily.
thanks|||Assuming that the combination of (account, name) is unique:
SELECT MIN(CASE seq WHEN 1 THEN name END)+
COALESCE(', '+MIN(CASE seq WHEN 2 THEN name END),'')+
COALESCE(', '+MIN(CASE seq WHEN 3 THEN name END),'')+
COALESCE(', '+MIN(CASE seq WHEN 4 THEN name END),'')
... etc...
account, MIN(balance)
FROM
(SELECT A1.name, A1.account, MIN(A1.balance), COUNT(*)
FROM AccountNames AS A1
JOIN AccountNames AS A2
ON A1.account = A2.account
AND A1.name >= A2.name
GROUP BY A1.name, A1.account)
AS A(name, account, balance, seq)
GROUP BY account
--
David Portas
--
Please reply only to the newsgroup
--|||david I need your help once agai
Main thing is I need the same output as before but I was not able to join my real world tables properly to get the data
I have three table
1. Person - which has (IdColumn(PK), PersonId, Firstname,Lastname,Middlename
2. UserPerson - which has (PersonId, UserId
3. PersonAccount - which has (IdColumn(PK), PersonId, JointAccount,Balance
Also all the personId's are GUID's
I need the userperson table because my where clause has a condition to get only persons beloging to a user. (like where userid = 100
My output as discussed previously should be
person name (Firstname+middlename+lastname), JointaccountId, balanc
The one which you given works fine with the previous table structure we have discussed
I very much appreciate your help
thank
-- David Portas wrote: --
Assuming that the combination of (account, name) is unique
SELECT MIN(CASE seq WHEN 1 THEN name END)
COALESCE(', '+MIN(CASE seq WHEN 2 THEN name END),'')
COALESCE(', '+MIN(CASE seq WHEN 3 THEN name END),'')
COALESCE(', '+MIN(CASE seq WHEN 4 THEN name END),''
... etc..
account, MIN(balance
FRO
(SELECT A1.name, A1.account, MIN(A1.balance), COUNT(*
FROM AccountNames AS A
JOIN AccountNames AS A
ON A1.account = A2.accoun
AND A1.name >= A2.nam
GROUP BY A1.name, A1.account
AS A(name, account, balance, seq
GROUP BY accoun
--
David Porta
--
Please reply only to the newsgrou
-|||SELECT MIN(CASE seq WHEN 1 THEN fullname END)+
COALESCE(', '+MIN(CASE seq WHEN 2 THEN fullname END),'')+
COALESCE(', '+MIN(CASE seq WHEN 3 THEN fullname END),'')+
COALESCE(', '+MIN(CASE seq WHEN 4 THEN fullname END),'')
jointaccount, MIN(balance)
FROM
(SELECT A1.personid, A1.jointaccount, MIN(A1.balance), COUNT(*)
FROM PersonAccount AS A1
JOIN PersonAccount AS A2
ON A1.jointaccount = A2.jointaccount
AND A1.personid >= A2.personid
GROUP BY A1.personid, A1.jointaccount)
AS A(personid, jointaccount, balance, seq)
JOIN
(SELECT personid,
COALESCE(firstname+' ','')+
COALESCE(middlename+' ','')+
COALESCE(lastname,'') AS fullname
FROM Person) AS P
ON A.personid = P.personid
GROUP BY jointaccount
--
David Portas
--
Please reply only to the newsgroup
--