Clever SQl req'd

[dewsbury]

I tried to sort this in work today but failed!
It looks easy ..but??

TABLE1:
Account Name
1001 John
1001 Paul
1002 Peter
1003 Joe
1001 Liam
1001 Cecil
1002 Oscar

Convert the above table to;

TABLE2:
Account Name1 name2 Name3 Name4
1001 John Paul Liam Cecil
1002 Peter Oscar
1003 Joe

 

[boblarson]

I tried to sort this in work today but failed!
It looks easy ..but??

TABLE1:
Account Name
1001 John
1001 Paul
1002 Peter
1003 Joe
1001 Liam
1001 Cecil
1002 Oscar

Convert the above table to;

TABLE2:
Account Name1 name2 Name3 Name4
1001 John Paul Liam Cecil
1002 Peter Oscar
1003 Joe

WHY? Why are you denormalizing?

 

[dewsbury]

Why denormalize?
A bit difficult to explain.

Here goes;

I am stuck in the middle of 2 sets of users.
I have been presented with the data in this format but a third party needs it in another format. It has to do with the printing of a mailing list - where some letters will be addressed to more than one member of a household (actually joint holders of a bank account).

I need to present the data to a letter printing company.
They have specified the format.

They will print tens of thousands of letters to bank customers.

The format of the data will be an account number followed by the persons names as account holders on that specific account.

The current format is not suitable.

 

[MagicMan]

This is interesting...
1) Create a query
2) Column 1...numbergroup field....count
3) Column 2...use expression builder ... Expr1: name where CountOfnumbergroup=1
4) Column 3...use expression builder ... Expr2: name where CountOfnumbergroup=2
Etc for as many possible names

Hope this helps.
Smiles
Bob

 

[redneckgeek]

Possible? Yes
Pretty? Probably Not
How to abuse a self-join (not as dirty as it sounds):

SELECT a.Account, Min([a.Name])+","+ nz(Min([b.name]),"")  + "," + nz(min([c.name]),"") AS [Names]
FROM (test AS a 
LEFT JOIN test AS b ON (a.Account = b.Account) AND (a.Name < .Name)) 
LEFT JOIN test c ON (b.Name < c.Name) AND (b.Account = c.Account)
GROUP BY a.Account

Figure out the MAXIMUM number of names you may have, and add another 10%. Do THAT many left joins. I went up to 25 and didn't have any problem. I did end up with extra commas on the rows that had fewer names associated, but you could probably fix that with some IIF's

 

[Banana]

To elaborate a bit, this is a real limitation of SQL; it sucks at recursive lookups, and the best we can do is to look up to a specified depth but this is still relatively ineffective compared to doing it in VBA/recordsets. Same problem exists for say, querying who is the immediate supervisor of a employee from a table of Employees of which several can be supervisors of each other.

MagicMan's approach is nice if you really want one-shot query. But if you need performance over simplicity, consider using VBA functions in the query:

SELECT DISTINCT Account, ConcatName(Account) FROM MyTable

ConccatName() would be a custom function you write... Something like this: (Aircode!)

Public Function ConcatName(Account As Long) As String

Dim rs As DAO.Recordset
Dim st As String

Set rs = CurrentDb.Openrecordset("SELECT Name FROM MyTable WHERE Account=" & Account)

With rs
   If Not .BOF And .EOF Then
      Do Until .EOF
         st = st & rs.Fields(0).Value & ", " 
         .MoveNext
      Loop
        st = Left(st, Len(st)-2)
      ConcatName= st
Else
      ConcatName = "No names associated with the accounts"
End If

End Function

Adjust as necessary for your need.


Redneckgeek- That's quite creative. I'll want to play with that... Thanks for sharing!

 

[redneckgeek]

Redneckgeek- That's quite creative. I'll want to play with that... Thanks for sharing!

I find I'm more creative in the evening with a few beers in me

/I should give some props here... I learned that from Pat Phelan over at dbforums a few years back

 

[georgedwilkinson]

To elaborate a bit, this is a real limitation of SQL; it sucks at recursive lookups, and the best we can do is to look up to a specified depth but this is still relatively ineffective compared to doing it in VBA/recordsets. Same problem exists for say, querying who is the immediate supervisor of a employee from a table of Employees of which several can be supervisors of each other.

I was appalled when I moved from Oracle to MS db products that you couldn't do this stuff more easily.

For the classic Employee/supervisor case, Oracle has the SQL "connect by" clause.

 

[Banana]

Really? I would so love to see that Connect By clause at work.... I was likewise surprised when MySQL folks told me that there wasn't a good way to do this and pointed to the SQL standard as falling short. Maybe they're right and Connect By is Oracle's departure from the standard, but still, I have to agree that this should be a part of standard as I'm quite sure that kind of case rears its ugly head a bit too often for our own good.

 

[georgedwilkinson]

I would so love to see that Connect By clause at work....

A sample from http://philip.greenspun.com/sql/trees.html:

select 
  [B]lpad(' ', (level - 1) * 2) || [/B]name as padded_name, 
  slave_id, 
  supervisor_id, 
  level
from corporate_slaves
connect by prior slave_id = supervisor_id
start with slave_id = 1;

PADDED_NAME			 SLAVE_ID SUPERVISOR_ID      LEVEL
------------------------------ ---------- ------------- ----------
Big Boss Man				1			 1
  VP Marketing				2	      1 	 2
  VP Sales				3	      1 	 2
    Joe Sales Guy			4	      3 	 3
      Bill Sales Assistant		5	      4 	 4
  VP Engineering			6	      1 	 2
    Jane Nerd				7	      6 	 3
    Bob Nerd				8	      6 	 3

8 rows selected.

Oracle has other similar, very complex, clauses (windows, etc.) for looking over a full result set and relating it in various ways.

 

[dewsbury]

As the original poster I vote top marks to "RedNeckGeek".

His code worked first time and while not very pretty (!) it did the job!

You can all have a beer with RNG now!

(I am sure that the other solutions worked as well)

 

[Uncle Gizmo]

I wondered if this thread might provide some inspiration