SQL Convert column to row

问题:

My table has the following columns:

A  |  B  |  C  |  D  |  E  |  F  

I want to displays this as follow:

MyColumn  |  MyColumn2
  A       |    B
  C       |    D
  E       |    F

As you can see i want to display the columns as pairs with a custom column name. The pairs are Column A and B, column C and D and column C and D.

回答1:

select A as [Col 1], B as [Col 2] from table
union all 
select C,D from table 
union all 
select E,F from table

回答2:

If I'm understanding you correctly you can do this with a union:

SELECT A as MyColumn, B as MyColumn2
UNION
SELECT C as MyColumn, D as MyColumn2
UNION
SELECT E as MyColumn, F as MyColumn2

If your datatypes aren't the same for the matching columns then you'll need to convert the datatype first, so something like:

SELECT CONVERT(VARCHAR(10), A) as MyColumn, CONVERT(VARCHAR(10), B) as MyColumn2
UNION
SELECT CONVERT(VARCHAR(10), C) as MyColumn, CONVERT(VARCHAR(10), D) as MyColumn2
UNION
SELECT CONVERT(VARCHAR(10), E) as MyColumn, CONVERT(VARCHAR(10), F) as MyColumn2

回答3:

This won't scale to a billion columns but...

Select A as MyColumn, B as MyColumn2 from Table
UNION
Select C as MyColumn, D as MyColumn2 from Table
UNION
Select E as MyColumn, F as MyColumn2 from Table

This query will remove the duplicates though. So say there's one record in the table

Hello | World | Hello | World | Hello | World

My select will only return

Hello | World

Sambo's will return all three...

回答4:

In Sql Server 2005 Unpivot operator can solve your problem. Unpivot converts columns to rows in sql server 2005.

Hope it helps