Transpose or reject each other column

Question

Basically what I'm trying to do is wrap each other column into a row with the following column data.

A source can have only two columns, up to one thousand and millions of rows. I do this only for each row. I tried to make "UNION ALL", however it is incredibly slow when you are dealing with hundreds of columns in a large table.

Possible solutions use UNPIVOT, although every example I can find does something different from what I want.

Example: before request

Columns... Apple | Apple Data | Banana | Banana Data | Cranberry | Cranberry Data | .... Data Returned... IOWA_PL_RLA | 1 | IOWA_PL_MATH | 1 | IOWA_SS_RLA 684 ..... 

Example: after request

 MeasureID Score CRCT_PL_RLA | 1 CRCT_PL_MATH | 1 CRCT_SS_RLA |684 ..... 

Hopefully I have explained enough that someone can help as I am only moderately experienced in T-SQL. If you need more information, please let me know.

Answer 1

If you are using SQL Server 2008, I think you should learn the CROSS APPLY (VALUES) technique:

http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Basically, it looks like this:

 SELECT x.NewColumn1, x.NewColumn2 FROM YourTable t CROSS APPLY ( VALUES (t.OldColumn1, t.OldColumn2), (t.OldColumn3, t.OldColumn4), ... ) x (NewColumn1, NewColumn2); 

Here is an example: http://sqlfiddle.com/#!3/9a9d2/5/0