Selecting multiple rows into a single row temp table

Question

What I am trying to do is select multiple rows into a single row of a temp table. There should be 1 row in the temp table listing all of the different accrual values. Here is the code below:

    declare @RunDate varchar(7)
set @RunDate = '2013-07'

declare @ShortItemNo1 int

set @ShortItemNo1 = 723639




declare @SalesAccruals table(QuantityShipped int, ShortItemNo int, GrossAmount money, AccrualReturns money, AccrualMedicaid money,
                            AccrualChargebacks money)

insert @SalesAccruals

select
    sum(s.QuantityShipped) QuantityShipped,
    s.ShortItemNo,
    sum(s.ExtendedPrice) ExtendedPrice,
    case when aa.AccrualType=3 then sum(a.AccrualAmount) end,
    case when aa.AccrualType=8 then sum(a.AccrualAmount) end,
    case when aa.AccrualType=2 then sum(a.AccrualAmount) end
from
    SalesSummary s join Accruals a on
        s.SalesSummaryGuid = a.SalesSummaryGuid
    join AccrualsSetup aa on
        a.AccrualsSetupGuid = aa.AccrualsSetupGuid
    join LookupAccrualTypes la on
        la.AccrualTypeID = aa.AccrualType

where
    convert(varchar(7), InvoiceDate, 20) = @RunDate
    and s.ShortItemNo in (@ShortItemNo1)
group by
    s.ShortItemNo,
    aa.AccrualType

select * from @SalesAccruals

Can you give sample data for what you want in the temp table? — David, Oct 24 '13 at 15:08
right now the select statement is returning four rows because there are four different accrual types. I want one row with all of the accrual types to be selected into the temp table. I am not sure if a case statement is the best way to go about this... — Jeff, Oct 24 '13 at 15:12
Right now it works the way I want, but the other values in the columns are null values. Any way to get rid of these? — Jeff, Oct 24 '13 at 15:16

score 0 · Accepted Answer · answered Oct 24 '13 at 15:21

0

Try changing the SELECT part of your query to be this...

select
    sum(s.QuantityShipped) QuantityShipped,
    s.ShortItemNo,
    sum(s.ExtendedPrice) ExtendedPrice,
    sum(case when aa.AccrualType=3 then a.AccrualAmount else 0 end),
    sum(case when aa.AccrualType=8 then a.AccrualAmount else 0 end),
    sum(case when aa.AccrualType=2 then a.AccrualAmount else 0 end)

Then remove aa.AccrualType from the GROUP BY.

answered Oct 24 '13 at 15:21

David

34,223
3
62
80

Worked like a charm. Thank you my friend. – Jeff Oct 24 '13 at 15:25

Selecting multiple rows into a single row temp table

1 Answers1