May 21, 2004 at 10:24 am
Hiya Guys,
I'm new to these forums hopefully this is the right place and 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 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
May 24, 2004 at 8:00 am
This was removed by the editor as SPAM
May 24, 2004 at 12:34 pm
Working with your example, I came up with:
DROP TABLE myData
GO
SET NOCOUNT ON
CREATE TABLE myData
(
id int PRIMARY KEY,
index1 int,
index2 int,
value varchar(50)
)
INSERT myData (id, index1, index2, value) VALUES (1, 1, 1, 'A')
INSERT myData (id, index1, index2, value) VALUES (2, 1, 2, '65')
INSERT myData (id, index1, index2, value) VALUES (3, 2, 1, 'B')
INSERT myData (id, index1, index2, value) VALUES (4, 2, 2, '66')
INSERT myData (id, index1, index2, value) VALUES (5, 3, 1, 'C')
INSERT myData (id, index1, index2, value) VALUES (6, 3, 2, '67')
INSERT myData (id, index1, index2, value) VALUES (7, 4, 1, 'D')
INSERT myData (id, index1, index2, value) VALUES (8, 4, 2, '68')
INSERT myData (id, index1, index2, value) VALUES (9, 5, 1, 'E')
INSERT myData (id, index1, index2, value) VALUES (10, 5, 2, '69')
SET NOCOUNT OFF
---------------------------------------------------------------------------------------
SELECT c1.index1 AS row,
c1.value AS col1,
c2.value AS col2
FROM myData c1 JOIN myData c2 ON c1.index1 = c2.index1
WHERE c1.value IN (SELECT d.value FROM myData d WHERE d.index1 = c1.index1 AND d.index2 = 1)
AND c2.value IN (SELECT d.value FROM myData d WHERE d.index2 = c2.index2 AND d.index2 = 2)
--Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply