A little SQL magic needed. Help! 
Author Message
 A little SQL magic needed. Help!

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



Mon, 10 May 2004 11:43:59 GMT  
 A little SQL magic needed. 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



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



Mon, 10 May 2004 13:24:20 GMT  
 A little SQL magic needed. Help!
SQL does not really lend itself to doing that kind of thing. Maybe you
should look at it another way. Does it matter whether they are listed
across or down? Once you've extracted them into a recordset you can
look at them any way you want to.


Tue, 11 May 2004 17:43:22 GMT  
 A little SQL magic needed. Help!
TRANSFORM First(test.Name) AS [The Value]
SELECT test.Key
FROM test
WHERE test.Key = 111
GROUP BY test.Key
PIVOT test.ID;

This worked great!!! I get:

Key         1                         2                     3
111         Smith                Jones               Zebra

Is there a way where I can the column names as something like "Name1", "Name2"
etc.

It's not a big deal, but it might help for this app. I'm working on.

I really appreciate the help .

Al


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



Wed, 12 May 2004 00:59:40 GMT  
 A little SQL magic needed. Help!
Hi Al,



Quote:
> TRANSFORM First(test.Name) AS [The Value]
> SELECT test.Key
> FROM test
> WHERE test.Key = 111
> GROUP BY test.Key
> PIVOT test.ID;

> This worked great!!! I get:

> Key         1                         2                     3
> 111         Smith                Jones               Zebra

> Is there a way where I can the column names as something like "Name1",
"Name2"
> etc.

That is the drawback with this method - the column headings are meaningless.
There are a couple of alternatives depending on your data.

If there is another piece of unique (per key) data relating to each of the
records in your table that you can add, you could use that for a heading. As
an example, suppose your table was recording players on a team. You could
then add a "Position" column (note that in this case, Key would refer to the
name of the team):

Key  Position           Name
---------------------------
111  Right Wing         Smith
111  Left Wing          Jones
111  Centre Forward     Zebra
222  Goalkeeper         Carter
222  Centre Half        Ford
222  Left Wing          Clinton

Changing the PIVOT line to read: 'PIVOT test.Position' would give you the
Position as a column header.

If something like this doesn't suit your data, you could simply change the
PIVOT line to read:

PIVOT "Name" & test.ID

But then using Key=222 would give headings of "Name4", "Name5", "Name6",
which is probably not too useful.

One way around that would be to change the autonumber field to a number
field and have the field filled in automatically during data entry using
some code that looks up the last number used by that key and returns the
next one in sequence.  Then your data would look like:

ID Key     Name
----------------
1  111     Smith
2  111     Jones
3  111     Zebra
1  222     Carter
2  222     Ford
3  222     Clinton

and you could use the method above to give you column headings of "Name1",
etc.

Richard



Wed, 12 May 2004 06:25:54 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Need a little more help with SQL

2. A little SQL help needed

3. Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

4. Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

5. a little help (for a little person)

6. Need help - a little off topic

7. Need a little help with a macro

8. Complicated Procedure, need just a little help

9. Need a little help :(

10. Need a little help with System.Management Please

11. Need a little help with variables on Crystal

12. Need a little help.

 

 
Powered by phpBB® Forum Software