four columnes into one

[asc4john]

I have a query that returns four columnes. What I need is to have the
results in one columne. Not added(+) or concatanated(&). For instance
100 rows, four columnes: becomes 400 rows one columne.
Thanks

 

[John Spencer]

Are the four columns all the same data type? If so, use a UNION ALL query.

SELECT [Column1]
FROM [YourTable]
UNION ALL

SELECT [Column2]
FROM [YourTable]
UNION ALL

SELECT [Column3]
FROM [YourTable]
UNION ALL

SELECT [Column4]
FROM [YourTable]

Union queries can only be created in the SQL window and not in the Query
Grid window. If you want only the unique values remove the word ALL from
UNION ALL

 

[Tom Ellison]

Dear John:

Write 4 queries that each return one of the 4 columns. Put them together
with a UNION.

I would add a column to each of these queries that identifies the source of
each. The finished UNION query would look like this:

SELECT "C1" AS Source, Column1
FROM YourTable
UNION ALL
SELECT "C2" AS Source, Column2
FROM YourTable
UNION ALL
SELECT "C3" AS Source, Column3
FROM YourTable
UNION ALL
SELECT "C4" AS Source, Column4
FROM YourTable

What you may want to do with duplicates could be to add DISTINCT after each
SELECT.

There's also the option to use UNION instead of UNION ALL, but it tends to
be slow.

Tom Ellison