Transform Columns into Rows *

Question

I have a very simple problem which requires a very quick and simple solution in SQL Server 2005.

I have a table with x Columns. I want to be able to select one row from the table and then transform the columns into rows.

TableA
Column1, Column2, Column3

SQL Statement to ruturn

ResultA
Value of Column1
Value of Column2
Value of Column3

---

@Kevin: I've had a google search on the topic but alot of the example where overly complex for my example, are you able to help further?

@Mario: The solution I am creating has 10 columns which stores the values 0 to 6 and I must work out how many columns have the value 3 or more. So I thought about creating a query to turn that into rows and then using the generated table in a subquery to say count the number of rows with Column >= 3

Answer

You should take a look at the UNPIVOT clause.

Update1: GateKiller, strangely enough I read an article (about something unrelated) about it this morning and I'm trying to jog my memory where I saw it again, had some decent looking examples too. It'll come back to me I'm sure.

Update2: Found it: http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/3470/" >Transform Columns into Rows< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.