如何将Sql列转换为行?

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

我有一个非常简单的问题，需要在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.

我有一个x列的表。我希望能够从表中选择一行，然后将列转换为行。

TableA
Column1, Column2, Column3

SQL Statement to ruturn

SQL语句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?

@Kevin:我已经对这个主题进行了谷歌搜索，但是对于我的例子来说，这个例子太复杂了，你能帮得上忙吗?

@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

@Mario:我正在创建的解决方案有10列，它们存储0到6的值，我必须计算出有多少列的值为3或更多。因此，我考虑创建一个查询来将其转换成行，然后在子查询中使用生成的表来计算列>= 3的行数。

6 个解决方案

#1

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.

更新1:GateKiller，奇怪的是，今天早上我读到一篇关于它的文章(一些无关的东西)，我试图唤起我的记忆，在我再次看到它的地方，也有一些不错的例子。我肯定它会回来找我的。

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

更新2:找到:http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx

#2

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.

我以前必须为一个项目做这个。我遇到的主要困难之一是解释我想要对其他人做什么。我花了大量的时间尝试在SQL中实现这一点，但我发现pivot函数非常不合适。我不记得它的确切原因，但对于大多数应用程序来说，它过于简单，而且在MS SQL 2000中还没有完全实现。我最后在。net中编写了一个pivot函数。我将把它贴在这里，希望有一天它能帮助别人。

 ''' <summary>
    ''' Pivots a data table from rows to columns
    ''' </summary>
    ''' <param name="dtOriginal">The data table to be transformed</param>
    ''' <param name="strKeyColumn">The name of the column that identifies each row</param>
    ''' <param name="strNameColumn">The name of the column with the values to be transformed from rows to columns</param>
    ''' <param name="strValueColumn">The name of the column with the values to pivot into the new columns</param>
    ''' <returns>The transformed data table</returns>
    ''' <remarks></remarks>
    Public Shared Function PivotTable(ByVal dtOriginal As DataTable, ByVal strKeyColumn As String, ByVal strNameColumn As String, ByVal strValueColumn As String) As DataTable
        Dim dtReturn As DataTable
        Dim drReturn As DataRow
        Dim strLastKey As String = String.Empty
        Dim blnFirstRow As Boolean = True

        ' copy the original data table and remove the name and value columns
        dtReturn = dtOriginal.Clone
        dtReturn.Columns.Remove(strNameColumn)
        dtReturn.Columns.Remove(strValueColumn)

        ' create a new row for the new data table
        drReturn = dtReturn.NewRow

        ' Fill the new data table with data from the original table
        For Each drOriginal As DataRow In dtOriginal.Rows

            ' Determine if a new row needs to be started
            If drOriginal(strKeyColumn).ToString <> strLastKey Then

                ' If this is not the first row, the previous row needs to be added to the new data table
                If Not blnFirstRow Then
                    dtReturn.Rows.Add(drReturn)
                End If

                blnFirstRow = False
                drReturn = dtReturn.NewRow

                ' Add all non-pivot column values to the new row
                For Each dcOriginal As DataColumn In dtOriginal.Columns
                    If dcOriginal.ColumnName <> strNameColumn AndAlso dcOriginal.ColumnName <> strValueColumn Then
                        drReturn(dcOriginal.ColumnName.ToLower) = drOriginal(dcOriginal.ColumnName.ToLower)
                    End If
                Next
                strLastKey = drOriginal(strKeyColumn).ToString
            End If

            ' Add new columns if needed and then assign the pivot values to the proper column
            If Not dtReturn.Columns.Contains(drOriginal(strNameColumn).ToString) Then
                dtReturn.Columns.Add(drOriginal(strNameColumn).ToString, drOriginal(strValueColumn).GetType)
            End If
            drReturn(drOriginal(strNameColumn).ToString) = drOriginal(strValueColumn)
        Next

        ' Add the final row to the new data table
        dtReturn.Rows.Add(drReturn)

        ' Return the transformed data table
        Return dtReturn
    End Function

#3

UNION should be your friend:

工会应该是你的朋友:

SELECT Column1 FROM table WHERE idColumn = 1
UNION ALL
SELECT Column2 FROM table WHERE idColumn = 1
UNION ALL
SELECT Column3 FROM table WHERE idColumn = 1

but it can also be your foe on large result sets.

但它也可能是你在大结果集上的敌人。

#4

If you have a fixed set of columns and you know what they are, you can basically do a series of subselects

如果你有一组固定的列你知道它们是什么，你基本上可以做一系列的子选择

(SELECT Column1 AS ResultA FROM TableA) as R1

(从表a中选择Column1作为ResultA)为R1

and join the subselects. All this in a single query.

并加入子选择。所有这些都在一个查询中。

#5

I'm not sure of the SQL Server syntax for this but in MySQL I would do

我不确定SQL服务器的语法，但在MySQL中我可以这么做

SELECT IDColumn, ( IF( Column1 >= 3, 1, 0 ) + IF( Column2 >= 3, 1, 0 ) + IF( Column3 >= 3, 1, 0 ) + ... [snip ] )
  AS NumberOfColumnsGreaterThanThree
FROM TableA;

EDIT: A very (very) brief Google search tells me that the CASE statement does what I am doing with the IF statement in MySQL. You may or may not get use out of the Google result I found

编辑:一个非常(非常)简短的谷歌搜索告诉我CASE语句的作用与我在MySQL中的IF语句的作用相同。您可能会使用我发现的谷歌结果，也可能不会

FURTHER EDIT: I should also point out that this isn't an answer to your question but an alternative solution to your actual problem.

进一步编辑:我还应该指出，这不是你的问题的答案，而是你实际问题的另一种解决方案。

#6

SELECT IDColumn, 
       NumberOfColumnsGreaterThanThree = (CASE WHEN Column1 >= 3 THEN 1 ELSE 0 END) + 
                                         (CASE WHEN Column2 >= 3 THEN 1 ELSE 0 END) + 
                                         (Case WHEN Column3 >= 3 THEN 1 ELSE 0 END) 
FROM TableA;