数据透视表中的文本值?
问题描述:
我有3列的表(在MySQL):数据透视表中的文本值?
Location Category Supplier
A Computers Company X
A Printers Company Y
B Computers Company X
B Printers Company Y
B Software Company Y
C Computers Company Y
C Software Company Z
现在我需要使含有上述信息的矩阵,如下所示:
Computers Printers Software
A Company X Company Y
B Company X Company Y Company Y
C Company Y Company Z
最终我需要有这Excel中。
实际上,我有一个可变数量的类别,所以在MySQL中使用每个列的连接来做它并不是一个好的选择。我可以用PHP写一个函数,但我想知道是否有更优雅的解决方案。
我在Excel中查看数据透视表,但它们似乎更适合作为数值的数字。但也许我忽略了一些东西,因为我从来没有用Excel自己工作。
任何想法的?
答
我也没有办法检查查询是好的,但或多或少会是这样的查询:
SELECT t1.Location, MAX(t1.Computers), MAX(t1.Printers), MAX(t1.Software)
FROM (
SELECT
t.Location,
CASE WHEN t.Category = 'Computers' THEN
t.Supplier
END Computers,
CASE WHEN t.Category = 'Printers' THEN
t.Supplier
END Printers,
CASE WHEN t.Category = 'Software' THEN
t.Supplier
END Software,
FROM
YOUR_TABLE t
) t1
GROUP BY t1.Location
答
我遇到了同样的问题,使用数据透视表...完美的总结,但不适用于文本矩阵。
我刚刚“举起”了一些我使用过的代码示例。在这里,我有列A-D中的数据,并在列F周围构建矩阵(在同一张表中)。
检查是否有帮助。
我仍然无法让代码看起来正确,所以请注意,代码窗口之前有很多代码开始。
代码示例1:
'Fill in the values
Sheets("TempFile").Select
ListRow = 1
MisMatchCounter = 0
Do Until Cells(ListRow, 1).Value = ""
' Get table entry from third column of list.
TableEntry = Cells(ListRow, 3).Value
On Error Resume Next
If Err.Number > 0 Then MsgBox Err.Number
' Get position of product name within range of row titles.
If TableEntry <> "" Then
TableRow = Application.Match(Cells(ListRow, 1), Range("F3:" & MYLastRowAddress), 0) ' 2 rows less than reality
' Get position of product size within range of column titles.
TableColumn = Application.Match(Cells(ListRow, 2), Range("G2:" & MYLastColAddress), 0)
Set CellToFill = Range("F2").Offset(TableRow, TableColumn)
' If there's already an entry in the cell, separate it from the new entry with a comma and space.
If Err.Number = 0 Then
If CellToFill.Value <> "" Then
CellToFill.Value = CellToFill.Value & ","
CellToFill.Value = CellToFill.Value & TableEntry
Else
CellToFill.Value = TableEntry
End If
Else
MisMatchCounter = MisMatchCounter + 1
Sheets("Errors").Cells(MisMatchCounter, 1).Value = ListRow
Sheets("Errors").Cells(MisMatchCounter, 2).Value = Cells(ListRow, 1)
Sheets("Errors").Cells(MisMatchCounter, 3).Value = Cells(ListRow, 2)
Sheets("Errors").Cells(MisMatchCounter, 4).Value = Cells(ListRow, 3)
Sheets("Errors").Cells(MisMatchCounter, 5).Value = Cells(ListRow, 4)
End If
End If
On Error GoTo 0
ListRow = ListRow + 1
Loop
代码示例2:
Sub CreateManualMatrix()
Dim TableRow, TableColumn As Integer
Dim TableEntry As String
Dim CellToFill As Range
'Sheet is called Lijst
'Column A is names for top row
'Column B is names for left column
'Column C is value for Matrix
'Matrix Top Row starts at H1
'Matrix Left Column starts at G2
MatrixLastColAddress = Range("H1").End(xlToRight).Address
MatrixLastRow = Range("G65536").End(xlUp).Row
LijstReadColumn = 3
LijstCurrentRow = 2 'make 1 if no header is used
Do Until Sheets("Lijst").Cells(LijstCurrentRow, 1).Value = ""
' Get table entry from third column of list.
TableEntry = Sheets("Lijst").Cells(LijstCurrentRow, LijstReadColumn).Value
' Get position of Employee name within Matrix.
TableColumn = Application.Match(Sheets("Lijst").Cells(LijstCurrentRow, 1), Range("H1:" & MatrixLastColAddress), 0)
' Get position of Qualification Name within Matrix titles.
TableRow = Application.Match(Sheets("Lijst").Cells(LijstCurrentRow, 2), Range("G2:G" & MatrixLastRow), 0)
Set CellToFill = Range("G1").Offset(TableRow, TableColumn)
' If there's already an entry in the cell, separate it from the new entry with a comma and space.
If CellToFill.Value <> "" Then CellToFill.Value = CellToFill.Value & ","
' Add the new entry to the cell.
CellToFill.Value = CellToFill.Value & TableEntry
LijstCurrentRow = LijstCurrentRow + 1
Loop
End Sub
答
你追求的是什么通常被称为交叉表。这可以静态地完成像这样:
Select Location
, Min(Case When Category = 'Computers' Then Supplier End) As Computers
, Min(Case When Category = 'Printers' Then Supplier End) As Printers
, Min(Case When Category = 'Software' Then Supplier End) As Software
From MyTable
Group By Location
但是,如果你追求的是有类别(因而列)的动态数量,这不能在SQL本机完成。这个后来的解决方案称为动态交叉表。最好的方法是在中间层构建类似于以上静态版本的SQL语句,或者使用报告工具来执行相同的操作。
[这听起来像你没有使用很多MS堆栈,所以我把它作为一个评论,而不是回答。] SQL Server Reporting Services将很好地处理这种情况,并为您提供出色的Excel输出。你可以从SSRS查询MySQL数据库。但是如果你没有MS SQL服务器,这是一个不好的选择。 –
您是否尝试过使用带有最大聚合函数的数据透视表? – PowerUser
尝试过,但不起作用。 – Dylan