Change Result Set Columns to Rows

I had a developer in my company come to me recently with a unique request. He asked if there was any way for sql to return a result set that was flopped. In other words he wanted the rows to be columns and the columns to be rows. So that a result set like this:

Col1 Col2 Col3A B CD E F

would look like this:

ColName Val1 Val2Col1 A DCol2 B ECol3 C F

Seems like this is one of those cool things you might can do with SQL but probably shouldn’t. Well I couldn’t resist the challenge, so here goes. If there is some better way to accomplish this I would love to see improvements.