sql – 数据透视表中的文本值?

前端之家收集整理的这篇文章主要介绍了sql – 数据透视表中的文本值?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个表(在MysqL中)有3列:
  1. Location Category Supplier
  2.  
  3. A Computers Company X
  4. A Printers Company Y
  5. B Computers Company X
  6. B Printers Company Y
  7. B Software Company Y
  8. C Computers Company Y
  9. C Software Company Z

现在我需要制作一个包含上述信息的矩阵,如下所示:

  1. Computers Printers Software
  2.  
  3. A Company X Company Y
  4. B Company X Company Y Company Y
  5. C Company Y Company Z

最终我需要在Excel中使用它.

实际上我有不同数量的类别,所以在MysqL中使用每列的连接进行操作并不是一个好选择.我可以在PHP中编写一个函数,但我想知道是否有更优雅的解决方案.

我在Excel中查看了一个数据透视表,但它们似乎更适合数字作为值.但也许我忽视了一些事情,因为我自己从不使用Excel.

有任何想法吗?

解决方法

我遇到了与数据透视表相同的问题…完美的摘要,但不适用于文本矩阵.

我刚刚“解除了”我使用过的一些代码示例.这里我有A-D列中的数据,并在F列周围构建矩阵(在同一张纸中).

检查一下这是否有帮助.

我仍然无法让代码看起来正确,所以请注意很多代码代码窗口之前启动.

代码示例1:

  1. 'Fill in the values
  2.  
  3. Sheets("TempFile").Select
  4.  
  5. ListRow = 1
  6.  
  7. MisMatchCounter = 0
  8.  
  9. Do Until Cells(ListRow,1).Value = ""
  10.  
  11. ' Get table entry from third column of list.
  12.  
  13. TableEntry = Cells(ListRow,3).Value
  14.  
  15. On Error Resume Next
  16.  
  17. If Err.Number > 0 Then MsgBox Err.Number
  18.  
  19. ' Get position of product name within range of row titles.
  20.  
  21. If TableEntry <> "" Then
  22.  
  23. TableRow = Application.Match(Cells(ListRow,1),Range("F3:" & MYLastRowAddress),0) ' 2 rows less than reality
  24.  
  25. ' Get position of product size within range of column titles.
  26.  
  27. TableColumn = Application.Match(Cells(ListRow,2),Range("G2:" & MYLastColAddress),0)
  28.  
  29. Set CellToFill = Range("F2").Offset(TableRow,TableColumn)
  30.  
  31. ' If there's already an entry in the cell,separate it from the new entry with a comma and space.
  32.  
  33. If Err.Number = 0 Then
  34.  
  35. If CellToFill.Value <> "" Then
  36.  
  37. CellToFill.Value = CellToFill.Value & ","
  38.  
  39. CellToFill.Value = CellToFill.Value & TableEntry
  40.  
  41. Else
  42.  
  43. CellToFill.Value = TableEntry
  44.  
  45. End If
  46.  
  47. Else
  48.  
  49. MisMatchCounter = MisMatchCounter + 1
  50.  
  51. Sheets("Errors").Cells(MisMatchCounter,1).Value = ListRow
  52.  
  53. Sheets("Errors").Cells(MisMatchCounter,2).Value = Cells(ListRow,1)
  54.  
  55. Sheets("Errors").Cells(MisMatchCounter,3).Value = Cells(ListRow,2)
  56.  
  57. Sheets("Errors").Cells(MisMatchCounter,4).Value = Cells(ListRow,3)
  58.  
  59. Sheets("Errors").Cells(MisMatchCounter,5).Value = Cells(ListRow,4)
  60.  
  61. End If
  62.  
  63. End If
  64.  
  65. On Error GoTo 0
  66.  
  67. ListRow = ListRow + 1
  68.  
  69. Loop

代码示例2:

  1. Sub CreateManualMatrix()
  2.  
  3. Dim TableRow,TableColumn As Integer
  4.  
  5. Dim TableEntry As String
  6.  
  7. Dim CellToFill As Range
  8.  
  9. 'Sheet is called Lijst
  10.  
  11. 'Column A is names for top row
  12.  
  13. 'Column B is names for left column
  14.  
  15. 'Column C is value for Matrix
  16.  
  17.  
  18.  
  19. 'Matrix Top Row starts at H1
  20.  
  21. 'Matrix Left Column starts at G2
  22.  
  23.  
  24.  
  25. MatrixLastColAddress = Range("H1").End(xlToRight).Address
  26.  
  27. MatrixLastRow = Range("G65536").End(xlUp).Row
  28.  
  29. LijstReadColumn = 3
  30.  
  31. LijstCurrentRow = 2 'make 1 if no header is used
  32.  
  33. Do Until Sheets("Lijst").Cells(LijstCurrentRow,1).Value = ""
  34.  
  35. ' Get table entry from third column of list.
  36.  
  37. TableEntry = Sheets("Lijst").Cells(LijstCurrentRow,LijstReadColumn).Value
  38.  
  39. ' Get position of Employee name within Matrix.
  40.  
  41. TableColumn = Application.Match(Sheets("Lijst").Cells(LijstCurrentRow,Range("H1:" & MatrixLastColAddress),0)
  42.  
  43. ' Get position of Qualification Name within Matrix titles.
  44.  
  45. TableRow = Application.Match(Sheets("Lijst").Cells(LijstCurrentRow,Range("G2:G" & MatrixLastRow),0)
  46.  
  47. Set CellToFill = Range("G1").Offset(TableRow,separate it from the new entry with a comma and space.
  48.  
  49. If CellToFill.Value <> "" Then CellToFill.Value = CellToFill.Value & ","
  50.  
  51. ' Add the new entry to the cell.
  52.  
  53. CellToFill.Value = CellToFill.Value & TableEntry
  54.  
  55. LijstCurrentRow = LijstCurrentRow + 1
  56.  
  57. Loop
  58.  
  59. End Sub

猜你在找的MsSQL相关文章