It's not a big deal, but it might help for this app. I'm working on.
I really appreciate the help .
> Hi Al,
> IF you always have the same number of entries for each 'key' (e.g. 3), and
> if there are never two entries the same for any key, then you can kludge it.
> It is ugly, and gets uglier if you have more than three:
> SELECT TOP 1 test.key, test.Name, test_1.Name, test_2.Name
> FROM (test INNER JOIN test AS test_1
> ON test.key = test_1.key)
> INNER JOIN test AS test_2
> ON test.key = test_2.key
> WHERE test_1.Name<>test.Name
> AND test_1.Name<>test_2.Name
> AND test_2.Name<>test.Name
> AND test_2.Name<>test_1.Name
> AND test.key=111;
> But if, as is more likely, you have a varying number of entries, then
> querying in this manner would require some code to build the sql.
> However, something you could experiment with is adding another field with a
> unique identifier for each record such as an autonumber, so that you have:
> Table: test
> ID Key Name
> ----------------
> 1 111 Smith
> 2 111 Jones
> 3 111 Zebra
> 4 222 Carter
> 5 222 Ford
> 6 222 Clinton
> Then, assuming you are using a database that supports the syntax (or
> something equivalent), you could use:
> TRANSFORM First(test.Name) AS [The Value]
> SELECT test.Key
> FROM test
> WHERE test.Key = 111
> GROUP BY test.Key
> PIVOT test.ID;
> This would give you the result that you desire.
> HTH,
> Richard
> > Is there an SQL statement that will "un-normalize" a table?
> > This is simple. I have a table with a "key" and a name:
> > 111 Smith
> > 111 Jones
> > 111 Zebra
> > 222 Carter
> > 222 Ford
> > 222 Clinton
> > I want to find an SQL statement that will return a recordset that has all
> of the
> > names in one row... when passing the parm for the "key":
> > 111 Smith Jones Zebra
> > Is this possible? What is the beginning of the SQL statement that ends
> > strSQL = "Select ........ From Table1 Where key = 111"
> > rst.open strSQL
> > Thanks to anyone who can help
> > Al