I want to merge date and amount into a string

Question

I am using the query which merges date column and amount column into a string, but as we are querying it by policy number, it is getting many rows, my thing is to merge these different values of the columns to one.

example:

2014/09/01 733.00
2012/08/01 322.02

so these are the different rows for one policy number, now I want them to be also in one single string as :

2014/09/01 733.00 2012/08/01 322.02

If they are two rows, then also the value of my column should merge all the values of dates and amounts into single string for that particular policy number.

The query I am using is :

SELECT
LEFT(CONVERT(VARCHAR, BSD.BILL_SCH_DTL_BILL_DT, 111), 10)
    + ' '+ CONVERT (VARCHAR(50),BSD.BILL_SCH_DTL_DRV_AMT ,128)Schedule_pymts, *
From POLICY_PERIOD_BILLING_SCHEDULE PB
JOIN BILLING_SCHEDULE_DETAIL BSD
        ON BSD.PLCY_PRD_BILL_SCH_ID = PB.PLCY_PRD_BILL_SCH_ID
        AND BSD.BILL_SCH_DTL_DRV_AMT <> 0
        AND BSD.VOID_IND = 'n'

but this only merges one month and one amount together, but I need all the months of amount to be in one string(column) for that particular policy number.

You may be able to use the function `CONCAT()` -- with `GROUP BY`, as Rahul says. — Smandoli, Sep 23 '14 at 14:14
possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Smandoli, Sep 23 '14 at 14:17

score 1 · Accepted Answer · edited May 23 '17 at 12:13

1

You need to use FOR XML PATH to concatenate your rows to columns. I think something like this will work:

SELECT  PB.*,
        RTRIM((SELECT   CONVERT(VARCHAR(10), BSD.BILL_SCH_DTL_BILL_DT, 111)+ ' '
                        + CONVERT(VARCHAR(50), BSD.BILL_SCH_DTL_DRV_AMT, 128) + ' '
                FROM    BILLING_SCHEDULE_DETAIL AS BSD
                WHERE   BSD.PLCY_PRD_BILL_SCH_ID = PB.PLCY_PRD_BILL_SCH_ID
                AND     BSD.BILL_SCH_DTL_DRV_AMT <> 0
                AND     BSD.VOID_IND = 'n'
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')) AS Schedule_pymts
FROM    POLICY_PERIOD_BILLING_SCHEDULE AS PB;

N.B. Don't use SELECT * - explicitly list your columns, but I don't know what they are so can't above

edited May 23 '17 at 12:13

Community

1
1

answered Sep 23 '14 at 14:19

GarethD

68,045
10
83
123

I tried the above code, but I am getting some errors on executing the code. Can you please check the code once again, Thanks in advance – Aparanjit Sep 23 '14 at 14:36
I have copied whole thing and, tried to execute from my side, But I got the error incorrect syntax near the keyword 'FROM' And it also shows red marks at pb.* and at line ').value('.', 'NVARCHAR(MAX)')) AS Schedule_pymts' ans at line 'FROM BILLING_SCHEDULE_DETAIL AS BSD' – Aparanjit Sep 23 '14 at 16:00
I think it was just an extra bracket in the subquery - please try again now. – GarethD Sep 23 '14 at 16:44

I want to merge date and amount into a string

1 Answers1