3 Adding sql tag for more views.
| link
2 Adding sql tag for more views.
| link
1
source | link

Collect a column as CSV in GroupBy

I have this schema:

Schema

Result Current Result

Story:

BILL_INFO has all bill headers. There is a business process called Deposit that occurs once a day: BILL_INFO are updated with a DEPOSIT_ID from DEPOSIT_INFO table and this creates a sort of group. Originally, accessing a set of BILL_INFO using a DEPOSIT_ID was enough.

Requirments:

Now, requirements have changed:

  • Each Deposit has to be categorized based on ACCOUNT_GROUP, CASHIER_ID, and PAY_MODE_ID
  • A new business process has been introduced that will work on a sub-group of a Deposited BILL_INFO set
  • I need to have the FOCUS_BILL_NUMBER as a CSV column
  • I have not been able to figure out how to break the DEPOSIT_INFO's DEPOSIT_AMOUNT when sub-grouping (DEPOSIT_AMOUNT = Total of the BILL_INFO.BILL_AMOUNT)

What I made so far:

SELECT TOP (100) PERCENT
  CAST(DI.DEPOSIT_DATE AS date) AS DepositDate,
  BI.ACCOUNT_GROUP + '|' + CAST(BI.CASHIER_ID AS varchar(10)) + '|' + CAST(BI.PAY_MODE_ID AS varchar(5))
  + '|' + CAST(DI.DEPOSIT_ID AS varchar(100)) AS DepositPart,
  BI.ACCOUNT_GROUP AS AccountGroup,
  BI.CASHIER_ID AS CashierId,
  DI.DEPOSIT_ID AS DepositId,
  BI.PAY_MODE_ID AS PaymentModeId,
  M_PM.NAME AS PaymentMode,
  CASE
    WHEN [BI].[LAST_MODIFIED_BY] = 'Kiosk' THEN [M_PM].[NAME] + ' (Kiosk)'
    ELSE [M_PM].[NAME]
  END AS PaymentModeSpecial,
  BI.LAST_MODIFIED_BY AS ModifiedBy,
  DI.DEPOSIT_AMOUNT AS DepositedAmount
FROM dbo.BILL_INFO AS BI
INNER JOIN dbo.MASTER_PAY_MODE AS M_PM
  ON BI.PAY_MODE_ID = M_PM.ID
INNER JOIN dbo.DEPOSIT_INFO AS DI
  ON BI.DEPOSIT_ID = DI.DEPOSIT_ID
WHERE (BI.DEPOSIT_ID NOT IN (SELECT
  DEPOSIT_ID
FROM dbo.BILL_INFO AS BII
WHERE (GRP_IS_SUBMITTED = 1)
AND (DEPOSIT_ID IS NOT NULL))
)
AND (BI.INPUT_STATUS = 1)
GROUP BY CAST(DI.DEPOSIT_DATE AS date),
         BI.ACCOUNT_GROUP + '|' + CAST(BI.CASHIER_ID AS varchar(10)) + '|' + CAST(BI.PAY_MODE_ID AS varchar(5)) + '|' + CAST(DI.DEPOSIT_ID AS varchar(100)),
         BI.ACCOUNT_GROUP,
         BI.CASHIER_ID,
         DI.DEPOSIT_ID,
         BI.PAY_MODE_ID,
         M_PM.NAME,
         CASE
           WHEN [BI].[LAST_MODIFIED_BY] = 'Kiosk' THEN [M_PM].[NAME] + ' (Kiosk)'
           ELSE [M_PM].[NAME]
         END,
         BI.LAST_MODIFIED_BY,
         DI.DEPOSIT_AMOUNT
ORDER BY DepositDate DESC, DepositId DESC