如上图所示,我正在尝试从 BEFORE 表中的数据中获得 AFTER 表中所示的结果。
这些是我希望我们实现的目标:
- 以唯一方式将部门从列转置为行
- 唯一列出每个位置、办公室和部门疲惫的所有供应商 - 多个供应商用逗号分隔
到目前为止,我已经能够编写一个能够复制部门数据并将其转置的子例程,但我正在向前发展。
Range("D4:D18").Select
Range("D4:D18").Sort key1:=Range("D4"), _
order1:=xlAscending, Header:=xlNo
Selection.Copy
Range("CJ3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
并在mySQL上尝试一下:
SELECT DISTINCT location, department, office, GROUP_CONCAT(vendor) as vendor FROM `tbl_dep_ven_location` WHERE department = 'Procurement' and location = 'Femty' GROUP BY office ORDER by depterment
这不是一个有效的查询,但它可以完成这项工作。
SELECT
A.location as location,
A.office as office,
(SELECT
GROUP_CONCAT(vendor) as Admin
FROM tbl_dep
WHERE location = A.location and office = A.office and department='Admin'
) as Admin,
(SELECT
GROUP_CONCAT(vendor) as Commerce
FROM tbl_dep
WHERE location = A.location and office = A.office and department='Commerce'
) as Commerce,
(SELECT
GROUP_CONCAT(vendor) as Law
FROM tbl_dep
WHERE location = A.location and office = A.office and department='Law'
) as Law,
(SELECT
GROUP_CONCAT(vendor) as Procurement
FROM tbl_dep
WHERE location = A.location and office = A.office and department='Procurement'
) as Procurement
FROM tbl_dep as A
GROUP BY A.location,A.office
ORDER BY A.location,A.office
如果您决定希望供应商订单按字母顺序排序,您只需在GROUP_CONCAT函数中添加 ORDER BY 子句。 如以下 SQL 所示。
SELECT
A.location as location,
A.office as office,
(SELECT
GROUP_CONCAT(vendor ORDER BY vendor) as Admin
FROM tbl_dep
WHERE location = A.location and office = A.office and department='Admin'
) as Admin,
(SELECT
GROUP_CONCAT(vendor ORDER BY vendor) as Commerce
FROM tbl_dep
WHERE location = A.location and office = A.office and department='Commerce'
) as Commerce,
(SELECT
GROUP_CONCAT(vendor ORDER BY vendor) as Law
FROM tbl_dep
WHERE location = A.location and office = A.office and department='Law'
) as Law,
(SELECT
GROUP_CONCAT(vendor ORDER BY vendor) as Procurement
FROM tbl_dep
WHERE location = A.location and office = A.office and department='Procurement'
) as Procurement
FROM tbl_dep as A
GROUP BY A.location,A.office
ORDER BY A.location,A.office