将行变为列

  Dim con As ADODB.Connection
  Dim Rs As ADODB.Recordset
  Dim sqlstr As String
 
  sqlstr = "SELECT SUM(CJ004) AS zf,xs001, xs002, xs003, xs004, xs005,  " & _
      "SUM(CASE cj003 WHEN '数学' THEN cj004 ELSE 0 END) AS maths," & _
      "SUM(CASE cj003 WHEN '英语' THEN cj004 ELSE 0 END) AS english," & _
      "SUM(CASE cj003 WHEN '语文' THEN cj004 ELSE 0 END) AS chinese " & _
      "into Temp FROM T_xs INNER JOIN T_cj ON xs001 = cj002  " & _
      "GROUP BY xs001, xs002, xs003, xs004, xs005"
  ExecuteSQL (sqlstr) '执行SQL语句
  sqlstr = "SELECT (SELECT COUNT(*) From Temp WHERE zf > A.zf) + 1 AS mc, A.* FROM Temp as A ORDER BY mc"
  Set con = New ADODB.Connection
  con.Open conn
  Set Rs = New ADODB.Recordset
  If Rs.State = adStateOpen Then
    Rs.Close
  End If
  With Rs
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open sqlstr, con, , , adCmdUnknown
  End With
  Set DataGrid1.DataSource = Rs
  sqlstr = "drop table Temp"