Belliez Belliez - 3 months ago 6
SQL Question

Return SQL Sub Query with a variable inside Select

My Application is a cash register. The following query takes a TransactionID and returns one or more rows of items sold in this single transaction i.e. for TransactionID = 28715:

DECLARE @SalesItems varchar(250);
DECLARE @TransactionID int;
SET @TransactionID = 28715;


Select @SalesItems = coalesce(@SalesItems + ', ', '') + CAST(TransactionsPosLines.Description as varchar(250))
From
TransactionsPosLines
where TransactionsPosLines.TransactionId = @TransactionID

select @SalesItems


and returns:

"Widget A, Widget B, Widget C" as a single string.


I also have an end of day transaction report which I want to append the string (Widget A etc...) onto the end of the transaction report.

Select
Transactions.TransactionId,
Transactions.TransactionDate,
Transactions.MoneyIn,
Transactions.MoneyOut,
Transactions.Description,
PaymentMethods.PaymentMethodName,
Transactions.TransactionRef,
Membership.Username,
Tills.Description As 'Till Name',
Transactions.Reason,
-- FOR THIS LAST COLUMN HERE I WANT TO SHOW THE OUTPUT OF THE QUERY ABOVE
====> SalesItems
From
Transactions Left Outer Join
Tills
On Transactions.TillId = Tills.TillId Inner Join
PaymentMethods
On Transactions.PaymentMethodId = PaymentMethods.PaymentMethodsID Inner Join
Membership
On Transactions.UserId = Membership.UserId Inner Join
Where
Transactions.TransactionDate >= @DateStart And
Transactions.TransactionDate <= @DateEnd


So when I run this report I get something like this:

TransactionId TransactionDate MoneyIn MoneyOut Description PaymentMethodName TransactionRef Username Till Name Reason SalesItems
28715 31/08/2016 09:07 119.99 0 Sale - Card Card 24881 Chantal Till1 Null Widget A, Widget B, Widget C

Answer

CROSS APPLY WITH FOR XML CONCATENATION METHOD:

Select
    t.TransactionId,
    t.TransactionDate,
    t.MoneyIn,
    t.MoneyOut,
    t.Description,
    p.PaymentMethodName,
    t.TransactionRef,
    m.Username,
    tl.Description As 'Till Name',
    t.Reason,
    c.SalesItems
From
    Transactions t
    LEFT JOIN  Tills tl
    ON t.TillId = tl.TillId
    INNER JOIN PaymentMethods p
    ON t.PaymentMethodId = p.PaymentMethodID
    INNER JOIN Membership m
    On t.UserId = m.UserId
    CROSS APPLY
    (SELECT STUFF(
        (SELECT ',' + CAST(tp.Description AS VARCHAR(100))
        FROM
            TransactionPostLines tp
        WHERE t.TransactionId = tp.TransactionId
        FOR XML PATH(''))
        ,1,1,'') as SalesItems) c
Where
    t.TransactionDate >= @DateStart
    AND t.TransactionDate <= @DateEnd

AS Sub Select in column Definition:

Select
    t.TransactionId,
    t.TransactionDate,
    t.MoneyIn,
    t.MoneyOut,
    t.Description,
    p.PaymentMethodName,
    t.TransactionRef,
    m.Username,
    tl.Description As 'Till Name',
    t.Reason,
    STUFF(
        (SELECT ',' + CAST(tp.Description AS VARCHAR(100))
        FROM
            TransactionPostLines tp
        WHERE t.TransactionId = tp.TransactionId
        FOR XML PATH(''))
        ,1,1,'') as SalesItems
From
    Transactions t
    LEFT JOIN  Tills tl
    ON t.TillId = tl.TillId
    INNER JOIN PaymentMethods p
    ON t.PaymentMethodId = p.PaymentMethodID
    INNER JOIN Membership m
    On t.UserId = m.UserId
Where
    t.TransactionDate >= @DateStart
    AND t.TransactionDate <= @DateEnd

Note using table aliases makes the code easier to read and to write!

Comments