How can I convert a table with horizontal data to a table with vertical data ?

[lhooker]

How can I convert a table with horizontal data to a table with vertical data ?

This example illustrates one row (Table 1) of data to many rows of data (Table 2).

Source
Table 1
Name Type Field1 Field2 Field3 Field4 Field5 Field6 Field7

Results
Table 2
Name Field1 Type
Name Field2 Type
Name Field3 Type
Name Field4 Type
Name Field5 Type
Name Field6 Type
Name Field7 Type

 

[June7]

UNION query.

SELECT Name, Type, Field1 AS Data, 1 AS SourceField FROM table1
UNION SELECT Name, Type, Field2, 2 FROM table1
UNION SELECT Name, Type, Field3, 3 FROM table1
UNION SELECT Name, Type, Field4, 4 FROM table1
UNION SELECT Name, Type, Field5, 5 FROM table1
UNION SELECT Name, Type, Field6, 6 FROM table1
UNION SELECT Name, Type, Field7, 7 FROM table1;

UNION will not allow duplicate records. If you want all data even if duplicates, use UNION ALL.

There is no query wizard or designer for UNION, must type or copy/paste into SQLWindow of the query builder.

 

[lhooker]

June7/Uncle Gizmo,

Thanks ! ! !