sql - 非规范化 SQL 查询和组合列

Question

我有一个需要以一种有趣的方式去规范化的查询。

这是来源:

SELECT
  BILL_Invoice.Invoice_Number as AccountNumber,
  replace(convert(varchar(10), dbo.BILL_Invoice.Invoice_Date,101) ,'/','') as AdmitDate,
  BILL_InvoiceCPT.InvoiceCPT_Code AS CPTCode,
  InvoiceCPT_FeeAmount as ItemCharge
FROM
  dbo.BILL_InvoiceCPT 
  LEFT JOIN dbo.BILL_Invoice
  ON dbo.BILL_InvoiceCPT.Invoice_ID = dbo.BILL_Invoice.Invoice_ID

输出如下所示:

AccountNumber | AdmitDate  | CPTCode | ItemCharge
38689         | 02192013   | 99213   |     110.00
38689         | 02192013   | 80053   |       0.00
38689         | 02192013   | 86361   |       0.00
38689         | 02192013   | 85025   |       0.00
38689         | 02192013   | 87536   |       0.00
38689         | 02192013   | 93000   |      25.00

我需要的是:

AccountNumber | AdmitDate | TotalCharges | CPTCodes                            | ItemCharges
38689         | 02192013  | 135.00       | 99213;80053;86361;85025;87536;93000 | 110.00;0.00;0.00;0.00;0.00;25.00

这是输入到 3rd 方软件应用程序所必需的。我不太确定如何正确地对这些信息进行非规范化。 PIVOT 函数会这样做吗？

Answer 1

MySQL 有一个名为 GROUP_CONCAT 的就绪函数。 !

它看起来像这样:

SELECT 
 AccountNumber
,AdmitDate
,SUM(TotalCharges)
,GROUP_CONCAT(CPTCode)
,GROUP_CONCAT(ItemCharges)
FROM tbl
WHERE condition
GROUP BY AccountNumber, AdmitDate