Ytukyg

I have this query that geneartes data in format also shown:

SELECT TOP (10)

       { FN CONCAT({ FN CONCAT(dbo.BILL_INFO.BILL_NUMBER, '-')}, REPLICATE('0', 2 - LEN(RTRIM(dbo.BILL_INFO.PAY_MODE_ID)))

                                                                 + RTRIM(dbo.BILL_INFO.PAY_MODE_ID))} AS Id,

       dbo.BILL_INFO.PAY_MODE_ID,

       dbo.MASTER_PAY_MODE.NAME AS PAY_MODE

FROM dbo.BILL_INFO

    INNER JOIN dbo.MASTER_PAY_MODE

        ON dbo.BILL_INFO.PAY_MODE_ID = dbo.MASTER_PAY_MODE.ID

ORDER BY dbo.BILL_INFO.BILL_DATE DESC;

I need to transpose the result to this format:

I can do this using excel and a pivot but is there a way using SQL query?

Sample Data

Id                |BILL_DATE              |PAY_MODE

0000056-1002-18-10|2018-11-26 14:03:03.553|Bank Transfer

0001199-1002-18-05|2018-11-26 13:58:25.763|Credit Card

0000162-1030-18-05|2018-11-26 13:55:40.590|Credit Card

0001198-1002-18-05|2018-11-26 13:49:39.013|Credit Card

0001859-1030-18-04|2018-11-26 13:44:23.333|Free

0004443-1002-18-03|2018-11-26 13:42:27.550|Debit

0001532-1031-18-03|2018-11-26 13:36:23.010|Debit

0001916-1002-18-04|2018-11-26 13:33:23.157|Free

0001915-1002-18-04|2018-11-26 13:32:45.653|Free

0001914-1002-18-04|2018-11-26 13:30:35.580|Free

0004442-1002-18-03|2018-11-26 13:24:11.730|Debit

0004441-1002-18-03|2018-11-26 13:22:35.020|Debit

0004440-1002-18-03|2018-11-26 13:12:01.920|Debit

0004439-1002-18-03|2018-11-26 13:10:06.483|Debit

0001197-1002-18-05|2018-11-26 13:07:19.673|Credit Card

0001196-1002-18-05|2018-11-26 13:02:31.527|Credit Card

0004438-1002-18-03|2018-11-26 13:00:01.000|Debit

0001003-1030-18-03|2018-11-26 12:57:42.630|Debit

0001531-1031-18-03|2018-11-26 12:56:33.210|Debit

0001913-1002-18-04|2018-11-26 12:54:41.077|Free

Update
I have taken the solution provided by @[Tim Biegeleisen] as follows:

SELECT

     MONTH(bi.BILL_DATE) AS [Month],

    MAX(CASE WHEN m.NAME = 'Cheque' THEN bi.PAY_MODE_ID END) AS Cheque,

    MAX(CASE WHEN m.NAME = 'Cash'   THEN bi.PAY_MODE_ID END) AS Cash,

    MAX(CASE WHEN m.NAME = 'Bank Transfer' THEN bi.PAY_MODE_ID END) AS [Bank Transfer],

    MAX(CASE WHEN m.NAME = 'Credit Card' THEN bi.PAY_MODE_ID END) AS [Credit Card],

    MAX(CASE WHEN m.NAME = 'Debit' THEN bi.PAY_MODE_ID END) AS Debit,

    MAX(CASE WHEN m.NAME = 'Free' THEN bi.PAY_MODE_ID END) AS Free

FROM dbo.BILL_INFO bi

INNER JOIN dbo.MASTER_PAY_MODE m

    ON bi.PAY_MODE_ID = m.ID

    WHERE YEAR(bi.BILL_DATE) = 2018

GROUP BY

     MONTH(bi.BILL_DATE) ORDER BY  MONTH(bi.BILL_DATE)

You may do so via a pivot query, perhaps something alone these lines:

SELECT

    YEAR(bi.BILL_DATE) + '-' + MONTH(bi.BILL_DATE) AS Date,

    COUNT(CASE WHEN m.NAME = 'Cheque' THEN 1 END) AS Cheque,

    COUNT(CASE WHEN m.NAME = 'Cash'   THEN 1 END) AS Cash,

    COUNT(CASE WHEN m.NAME = 'Bank Transfer' THEN 1 END) AS [Bank Transfer],

    COUNT(CASE WHEN m.NAME = 'Credit Card' THEN 1 END) AS [Credit Card],

    COUNT(CASE WHEN m.NAME = 'Debit' THEN 1 END) AS Debit,

    COUNT(CASE WHEN m.NAME = 'Free' THEN 1 END) AS Free

FROM dbo.BILL_INFO bi

INNER JOIN dbo.MASTER_PAY_MODE m

    ON bi.PAY_MODE_ID = m.ID

GROUP BY

    YEAR(bi.BILL_DATE) + '-' + MONTH(bi.BILL_DATE);

Note that I am grouping the date column by month and year, because a given month appearing in the BILL_INFO table could belong to more than one year, in general.