Building a List from a result set to use as a column in another Query

…the title doesn’t explain the scenario very well but hopefully the description below will…

Scenario

A report needs to be generated which has a maximum of 1 row per order. Each order can have multiple voucher numbers, and these voucher numbers must be visible for that order on that single row.

At first glance, this isn’t possible. Why? because it is not possible to group unique voucher numbers which reside as different rows onto 1 row together with maintaining the integrity of the data. In the standard scenario, those voucher numbers will always group separately, hence creating multiple rows per order.

For example:

SELECT a.order_id, a.apar_id, sum(a.amount), a.voucher_no, a.sequence_no, b.arr_amount, b.real_amount, b.com_amount, b.vow_amount
FROM acrtrans a INNER JOIN apodetail b
ON ( a.client=b.client
    and a.order_id=b.order_id
    and a.account=b.account
    and a.apar_id=b.apar_id) WHERE a.client = 'TM' AND a.order_id = '405604' GROUP BY a.order_id, a.apar_id, a.sequence_no, a.voucher_no, b.arr_amount, b.real_amount, b.com_amount, b.vow_amount

image

As the above indicates its not possible to get this into one row, as the unique voucher numbers dictate the number of rows.

So how do we get all this info on to 1 row?

Im sure there are different methods but this worked ok for me:

First, the idea is to collate the voucher numbers for each order and present them on 1 row. We do this by creating a function to push in the order id, and extract the vouchers for that order id, and clean up and format into 1 column and row.

CREATE FUNCTION [dbo].[Get_Voucher_Numbers](@order_id int) RETURNS VARCHAR(1000) BEGIN DECLARE @VoucherList varchar(1000) SELECT @VoucherList = coalesce(@VoucherList + ', ', '') + CONVERT(varchar(8),a.voucher_no) FROM acrtrans a inner join apodetail b
        ON (a.client=b.client
        and a.order_id=b.order_id
        and a.account=b.account
        and a.apar_id=b.apar_id) where a.client = 'TM' AND a.order_id = @order_id
    ORDER BY a.voucher_no

RETURN @VoucherList

END GO

Running this for the example above returns the following:

image

Now the idea is to build this into the original SQL so that the voucher number becomes irrelevant in the group by.

SELECT a.order_id, a.apar_id, sum(a.amount), dbo.Get_Voucher_numbers(a.Order_id) AS Voucher_Numbers, a.sequence_no, b.arr_amount, b.real_amount, b.com_amount, b.vow_amount
FROM acrtrans a INNER JOIN apodetail b
ON ( a.client=b.client
    and a.order_id=b.order_id
    and a.account=b.account
    and a.apar_id=b.apar_id) WHERE a.client = 'TM' AND a.order_id = '405604' GROUP BY a.order_id, a.apar_id, a.sequence_no, b.arr_amount, b.real_amount, b.com_amount, b.vow_amount

image

As can be seen above, we now see all 3 voucher numbers for that order on a single line.

If anyone has a better way of doing this please let me know… I did start looking at CTE but got a bit unstuck!

Advertisements

One Response to Building a List from a result set to use as a column in another Query

  1. Jpseed says:

    It is always difficult to get knowledgeable people with this issue, nevertheless, you be understood as you understand exactly what you are posting about! Appreciate it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: