如何将SQL列转换为行?

您应该看看UNPIVOT子句。

更新1 :gatekiller,奇怪的是,今天早上我读了一篇关于它的文章(关于一些无关的东西),我试图把我的记忆放到我再次看到它的地方,也有一些好看的例子。我肯定会回来的。

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

我以前为一个项目做过这个。我遇到的一个主要困难是解释我试图对其他人做什么。我花了很多时间在SQL中尝试这样做,但是我发现pivot函数严重不足。我不记得它的确切原因,但对于大多数应用程序来说,它太简单了,而且在MS SQL 2000中没有完全实现。最后我在.NET中编写了一个透视函数。我会把它贴在这里,希望有一天它能帮助别人。

 ''' <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

UNION 应该是你的朋友:

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

但它可以 also be your foe 在大型结果集上。

如果您有一组固定的列,并且知道它们是什么,那么基本上可以执行一系列子选择

(SELECT Column1 AS ResultA FROM TableA) as R1

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

我不确定这方面的SQL Server语法,但在MySQL中我会这样做。

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

编辑:一个非常(非常)简短的谷歌搜索告诉我 CASE 声明做了我正在做的 IF MySQL中的语句。你可能会也可能不会利用 the Google result I found

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

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;