SQLTeam.com | Weblogs | Forums

Show a record mutlipe time in recordset


#1

I have the following query

select sfirstname,slastname,saddress1,saddress2,scity,sstate,szip,c.country from vworders o left join countries c on c.id=o.scountry left join invoices i on i.orderid=o.orderid and i.ordertype=o.ordertype where

    invoiceid IN (1,2,2,2,3,3,4,5,6)

what I really want is for each time the id appears - it should show that row again - can this be done in sql or do I have to do it on the frontend side?

for example order 1 would show once , order 2 would show 3 times


#2

Make a temp table with your invoiceids and join into your query:

create table #inv(invoiceid int);
insert into #inv(invoiceid) values(1),(2),(2),(2),(3),(3),(4),(5),(6);
select ...
  from ...
       inner join #inv
               on #inv.invoiceid=yourtable.invoiceid
;
drop table #inv;

#3

How is the CSV in the WHERE IN supplied? Does it come from a parameter in a Stored Procedure?