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

<p>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.</p>

<pre><code>TableA
Column1, Column2, Column3
</code></pre>

<p>SQL Statement to ruturn</p>

<pre><code>ResultA
Value of Column1
Value of Column2
Value of Column3
</code></pre>

<hr>

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

<p>@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</p>

[{'answer_id': 3473, 'author': 'Kev', 'author_id': 419, 'author_profile': 'https://Stackoverflow.com/users/419', 'pm_score': 4, 'selected': True, 'text': '<p>You should take a look at the UNPIVOT clause.</p>\n\n<p><strong>Update1</strong>: 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.</p>\n\n<p><strong>Update2</strong>: Found it: <a href="http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx" rel="nofollow noreferrer">http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx</a></p>\n'}, {'answer_id': 3475, 'author': 'Michael Stum', 'author_id': 91, 'author_profile': 'https://Stackoverflow.com/users/91', 'pm_score': 0, 'selected': False, 'text': '<p><a href="http://msdn.microsoft.com/en-us/library/ms180026.aspx" rel="nofollow noreferrer">UNION</a> should be your friend:</p>\n\n<pre><code>SELECT Column1 FROM table WHERE idColumn = 1\nUNION ALL\nSELECT Column2 FROM table WHERE idColumn = 1\nUNION ALL\nSELECT Column3 FROM table WHERE idColumn = 1\n</code></pre>\n\n<p>but it can <a href="http://blog.falafel.com/2006/01/20/TSQLTipAvoidUNION.aspx" rel="nofollow noreferrer">also be your foe</a> on large result sets.</p>\n'}, {'answer_id': 3478, 'author': 'Joseph Daigle', 'author_id': 507, 'author_profile': 'https://Stackoverflow.com/users/507', 'pm_score': 0, 'selected': False, 'text': '<p>If you have a fixed set of columns and you know what they are, you can basically do a series of subselects </p>\n\n<p><code>(SELECT Column1 AS ResultA FROM TableA) as R1</code> </p>\n\n<p>and join the subselects. All this in a single query.</p>\n'}, {'answer_id': 3513, 'author': 'Mat', 'author_id': 48, 'author_profile': 'https://Stackoverflow.com/users/48', 'pm_score': 0, 'selected': False, 'text': '<p>I\'m not sure of the SQL Server syntax for this but in MySQL I would do</p>\n\n<pre><code>SELECT IDColumn, ( IF( Column1 &gt;= 3, 1, 0 ) + IF( Column2 &gt;= 3, 1, 0 ) + IF( Column3 &gt;= 3, 1, 0 ) + ... [snip ] )\n  AS NumberOfColumnsGreaterThanThree\nFROM TableA;\n</code></pre>\n\n<p>EDIT: A very (very) brief Google search tells me that the <code>CASE</code> statement does what I am doing with the <code>IF</code> statement in MySQL.  You may or may not get use out of <a href="http://www.craigsmullins.com/ssu_0899.htm" rel="nofollow noreferrer">the Google result I found</a></p>\n\n<p>FURTHER EDIT: I should also point out that this isn\'t an answer to your question but an alternative solution to your actual problem.</p>\n'}, {'answer_id': 3533, 'author': 'Shawn', 'author_id': 26, 'author_profile': 'https://Stackoverflow.com/users/26', 'pm_score': 1, 'selected': False, 'text': '<p>I had to do this for a project before. One of the major difficulties I had was explaining what I was trying to do to other people. I spent a ton of time trying to do this in SQL, but I found the pivot function woefully inadequate. I do not remember the exact reason why it was, but it is too simplistic for most applications, and it isn\'t full implemented in MS SQL 2000. I wound up writing a pivot function in .NET. I\'ll post it here in hopes it helps someone, someday. </p>\n\n<pre><code> \'\'\' &lt;summary&gt;\n    \'\'\' Pivots a data table from rows to columns\n    \'\'\' &lt;/summary&gt;\n    \'\'\' &lt;param name="dtOriginal"&gt;The data table to be transformed&lt;/param&gt;\n    \'\'\' &lt;param name="strKeyColumn"&gt;The name of the column that identifies each row&lt;/param&gt;\n    \'\'\' &lt;param name="strNameColumn"&gt;The name of the column with the values to be transformed from rows to columns&lt;/param&gt;\n    \'\'\' &lt;param name="strValueColumn"&gt;The name of the column with the values to pivot into the new columns&lt;/param&gt;\n    \'\'\' &lt;returns&gt;The transformed data table&lt;/returns&gt;\n    \'\'\' &lt;remarks&gt;&lt;/remarks&gt;\n    Public Shared Function PivotTable(ByVal dtOriginal As DataTable, ByVal strKeyColumn As String, ByVal strNameColumn As String, ByVal strValueColumn As String) As DataTable\n        Dim dtReturn As DataTable\n        Dim drReturn As DataRow\n        Dim strLastKey As String = String.Empty\n        Dim blnFirstRow As Boolean = True\n\n        \' copy the original data table and remove the name and value columns\n        dtReturn = dtOriginal.Clone\n        dtReturn.Columns.Remove(strNameColumn)\n        dtReturn.Columns.Remove(strValueColumn)\n\n        \' create a new row for the new data table\n        drReturn = dtReturn.NewRow\n\n        \' Fill the new data table with data from the original table\n        For Each drOriginal As DataRow In dtOriginal.Rows\n\n            \' Determine if a new row needs to be started\n            If drOriginal(strKeyColumn).ToString &lt;&gt; strLastKey Then\n\n                \' If this is not the first row, the previous row needs to be added to the new data table\n                If Not blnFirstRow Then\n                    dtReturn.Rows.Add(drReturn)\n                End If\n\n                blnFirstRow = False\n                drReturn = dtReturn.NewRow\n\n                \' Add all non-pivot column values to the new row\n                For Each dcOriginal As DataColumn In dtOriginal.Columns\n                    If dcOriginal.ColumnName &lt;&gt; strNameColumn AndAlso dcOriginal.ColumnName &lt;&gt; strValueColumn Then\n                        drReturn(dcOriginal.ColumnName.ToLower) = drOriginal(dcOriginal.ColumnName.ToLower)\n                    End If\n                Next\n                strLastKey = drOriginal(strKeyColumn).ToString\n            End If\n\n            \' Add new columns if needed and then assign the pivot values to the proper column\n            If Not dtReturn.Columns.Contains(drOriginal(strNameColumn).ToString) Then\n                dtReturn.Columns.Add(drOriginal(strNameColumn).ToString, drOriginal(strValueColumn).GetType)\n            End If\n            drReturn(drOriginal(strNameColumn).ToString) = drOriginal(strValueColumn)\n        Next\n\n        \' Add the final row to the new data table\n        dtReturn.Rows.Add(drReturn)\n\n        \' Return the transformed data table\n        Return dtReturn\n    End Function\n</code></pre>\n'}, {'answer_id': 142124, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<pre><code>SELECT IDColumn, \n       NumberOfColumnsGreaterThanThree = (CASE WHEN Column1 &gt;= 3 THEN 1 ELSE 0 END) + \n                                         (CASE WHEN Column2 &gt;= 3 THEN 1 ELSE 0 END) + \n                                         (Case WHEN Column3 &gt;= 3 THEN 1 ELSE 0 END) \nFROM TableA;\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/3470', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/383/']