Columns to Rows 1

[mayamanako]

Hi guys, can you give me some ideas as to how I'd tackle this problem please?

My table has 3 columns, all text:
col1 col2 col3

How do I make it into a recordset that looks something like this?
col1
col2
col3

Thanks for any suggestions?

 

[draigGoch]

You need to use a Cross-tab/Pivot query.
For example, you have a table called tblSales:

Year | Sales
--------------
2011 | 10000
2010 | 9000
2009 | 8000

SELECT [2009],[2010],[2011]
FROM tblSales
PIVOT
(
MAX(Sales)
FOR [Year] IN ([2011],[2010],[2009]);
)
AS mySales

Will Output:

2009 | 2010 | 2011
-------------------------
10000 | 9000 | 8000

A computer always does what you tell it to, but rarely does what you want it to.....

 

[r937]

draigGoch, i think what the original poster is after is "unpivot"

My table has 3 columns, all text:
col1 col2 col3

How do I make it into a recordset that looks something like this?
col1
col2
col3

SELECT col1 FROM daTable
UNION ALL
SELECT col2 FROM daTable
UNION ALL
SELECT col3 FROM daTable

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mayamanako]

Wow, thanks very much r937, it worked like magic!

 

[draigGoch]

Ah yes - That should teach me to read it properly!

A computer always does what you tell it to, but rarely does what you want it to.....