Convert row values to column names in SQL Server

Question

I wasn't able to find a answer to my question and I'm breaking my head for this problem for a couple of days.

Basically I have 3 tables:

Receipt, Receiptline, Receiptlinedetail

Short summary of why these 3:

• Receipts - List of all receipts (eg: receipt 100)
• Receiptline - Each line of the receipt (eg: receipt 100 has been payed with Visa)
• Receiptlinedetail - Detailed information of the receiptline (eg: The NetAmount was 50 euro)

Now I want to have an horizontal overview of all the payments that have been done per day.

Output should be like this:

Day       | Visa | Mastercard | Cash 
2020-11-30  20.00    10.00       5.00

My data:

Table receipt:

ReceiptId   ReceiptNumber   ReceiptType   ReceiptDateTime      ModifiedKind
----------------------------------------------------------------------------
44919            1             100        2020-08-15 13:49:45.633   100
44920            1             200        2020-08-15 13:50:15.060   100
44921            2             100        2020-08-15 13:54:14.007   100
44922            2             300        2020-08-15 13:55:31.650   100
44923            3             100        2020-08-15 13:55:38.263   100
44924            3             300        2020-08-15 13:56:47.677   100
44925            4             100        2020-08-15 13:56:58.940   100
44926            4             200        2020-08-15 13:57:13.707   100

Table receiptline:

ReceiptLineId   ReceiptId   LineType    ItemId  Description     Quantity    ModifiedKind
----------------------------------------------------------------------------------------
89471             44919      300          0      GlobalInfo     1.0000      100
89472             44920      100         225     Sprite         4.0000      100
89473             44920      200          3      Cash           1.0000      100
89474             44920      300          0      GlobalInfo     1.0000      100
89475             44921      300          0      GlobalInfo     1.0000      100
89476             44922      300          0      GlobalInfo     1.0000      100
89477             44923      300          0      GlobalInfo     1.0000      100
89478             44924      300          0      GlobalInfo     1.0000      100
89479             44925      300          0      GlobalInfo     1.0000      100
89480             44926      200          6      VISA           1.0000      100

Table receiptlinedetail:

ReceiptLineDetailId ReceiptLineId   LineType    ItemId  Description Quantity    NetAmount   ModifiedKind
---------------------------------------------------------------------------------------------------------
89493               89471             300         0      GlobalInfo 1.0000      0.00       100
89494               89472             100         225    Sprite     4.0000      8.00       100
89495               89473             200         3      Cash       1.0000      -8.00      100
89496               89474             300         0      GlobalInfo 1.0000      0.00       100
89497               89475             300         0      GlobalInfo 1.0000      0.00       100
89498               89476             300         0      GlobalInfo 1.0000      0.00       100
89499               89477             300         0      GlobalInfo 1.0000      0.00       100
89500               89478             300         0      GlobalInfo 1.0000      0.00       100
89501               89479             300         225    Sprite     1.0000      2.00       100
89502               89482             300         0      GlobalInfo 1.0000      0.00       100
89503               89480             200         6      VISA       1.0000      -2.00       100

I have also a payment table where all the different payment options are listed.

PaymentId   Description     PaymentType ModifiedKind
----------------------------------------------------
1           Diners Club      200        200
2           Mastercard       200        200
3           Cash             100        200
4           Mobile payment   100        200
5           Shopmaster       500        200
6           VISA             200        200

Short summary:

All the payments from the receipts are 'LineType 200'. The amount of payment methods can differ from database to database, but is always listed in the payment table.

Answer 1

The only approach I think of is PIVOTING with Dynamic SQL.

Try this:

DECLARE @columns VARCHAR(MAX) = '';
declare @sql     NVARCHAR(MAX) = '';


SELECT 
    @columns+=QUOTENAME(Description) + ','
FROM 
    payment
ORDER BY 
    Description;

SET @columns = LEFT(@columns, LEN(@columns) - 1);

SET @sql = '
select *
from(

select convert(date, r.ReceiptDateTime) as Day,
       p.Description,
       rld.netamount
    
from receipt r 
    join receiptline rl
        on rl.ReceiptId = r.ReceiptId 
    join receiptlinedetail rld
        on rld.ReceiptLineId = rl.ReceiptLineId 
    join payment p
        on p.paymentid = rl.itemid and rl.LineType = 200
) d pivot (
        sum(netamount)
        for Description in (' + @columns + ')
) as t;'

EXECUTE sp_executesql @sql;

Answer 2

I think the linking to Payments is through the itemId column. The rest is just joins and conditional aggregation:

select convert(date, r.ReceiptDateTime),
       sum(case when p.Description = 'Visa' then rld.net_amount else 0 end) as visa,
       sum(case when p.Description = 'MasterCard' then rld.net_amount else 0 end) as mastercard,
       sum(case when p.Description = 'Cash' then rld.net_amount else 0 end) as cash
from receipt r join
     receiptline rl
     on rl.ReceiptId = r.ReceiptId join
     receiptlinedetail rld
     on rld.ReceiptLineId = rl.ReceiptLineId join
     payments p
     on p.paymentid = rl.itemid and rl.LineType = 200
group by convert(date, r.ReceiptDateTime)