SQL: is this possible

[neildodsworth]

Hiya Guys,

I'm a little new to these forums but I hope you can help..

If I have a very simple table as follows:

ID : Integer
Index1 : Integer
Index2 : Integer
Value : String

The ID is a unique identifier, index1 and index2 fields are used to stipulate a two dimensional array with the value being the contents of aforesaid array.

Is it possible via SQL to create a query (or multiple) that will return the data in the following form.

Index2 val = 1 : Index2 = 2 : Index2 =3 etc....

Index1 Value : Value : Value
Index1 Value : Value : Value
Index1 Value : Value : Value
Index1 Value : Value : Value
Index1 Value : Value : Value
Index1 Value : Value : Value

etc....

I know that the above might be a little odd, but its a simplification of some dynamic data manipulation I'm doing in a softare project.

Unfortunately the table as described is fixed and not under my control.

A real world example could be:

Table Contains:

ID Index1 Index2 Value
1 1 1 A
2 1 2 65
3 2 1 B
4 2 2 66
5 3 1 C
6 3 2 67

I'd like an SQL Statement that returns

1 2 (effectively Index2)
1 A 65
2 B 66
3 C 67

Thanks for any help.

Neil

 

[swampBoogie]

SQL>create table t(i1 int,i2 int, v char(2));
SQL>insert into t values(1,1,'A');
SQL>insert into t values(1,2,'65');
SQL>insert into t values(2,1,'B');
SQL>insert into t values(2,2,'66');
SQL>insert into t values(3,1,'C');
SQL>insert into t values(3,2,'67');
SQL>
SQL>select distinct q.i1 as " "
SQL&,(select v from t where i2 = 1 and i1 = q.i1) as "1",
SQL&            (select v from t where i2 = 2 and i1 = q.i1) as "2"
SQL&from t q;
            1  2
=========== == ==
          1 A  65
          2 B  66
          3 C  67

                  3 rows found

 

[neildodsworth]

Thanks for that,

My SQL skills aren't that great but I follow pretty much most of what you have said there except the specifics of "q" could you clarify what this is and how it comes into being...

is it a reference back to the query...

I know this is probably swearing but I'm trying to relate what you have said into an access query.

Thanks for the help...

 

[PHV]

You can read this:
from t q;
as this:
from t [highlight]As[/highlight] q;
so q is an alias for t, needed to avoid confusion in the subqueries.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[neildodsworth]

Thanks for that, helped a lot. I have the query running... great. However I was hoping that I would be able to edit the values within the dataset returned but i can't... ?

I'm presuming this is because the key field can't be derived from the row.... is there any way around this?

Extra Info:
I was hoping to overlay a datbase component in my app onto the dataset returned to provide the user with the means to change the value field. I can overlay the component fine, but it won't allow editing.

Tahnks again for the help.

 

[neildodsworth]

Would it be advisable to take the progress so far and place it in the domain of a MS Access expert...

thanks so far though guys.