need select statement

I have one table with the following fields:
quote_info: quote_number, reps, location, model, product, job_title

this is messy but i want to get the details for each distinct quote_number, product pair and the reps that have job_title like 'sales_rep'

so a simple distinct isn't going to cut it here... I need the details for each quote_number, product combination...
acdagirlAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

adraughnCommented:
look at the results from this:

SELECT DISTINCT quote_number, reps, location, model, product, job_title
FROM quote_info
WHERE job_title = 'sales_rep'

how does that compare to what you are looking for?
0
LowfatspreadCommented:
you need to give us some example data and results to work on ...

your requirements are to imprecise...

set the scence and tell us what your trying to achieve,,,

e.g.

sales quotes are added to quote_info , a quote can have many products, many sales men can work on a quote..
i need to see ....

 
0
acdagirlAuthor Commented:
i think the problem is that I want the number of line items to be the count(distinct quote_number, product) so I need some sort of aggregate table...

quote_number  product   employee .....
123                   laptop     abe, bob, jane
123                   mouse    bob, julie
352                   laptop     bubba

like that, rather than

123        laptop    abe
123        laptop   bob
....
0
Jon_RaymondCommented:
this is quite complex. You probably need a temp table to return the rep names and you'll need to loop through it for each main record. You'll also need a main temp table.

The main table, #Temp1, will have all the fields, except the REP field will be a varchar(max) field.
#Temp1 also will need a IDENTITY field, call it "QnumID" or something.

create #Temp2 for the REP data. It will have an ID int field, not IDENTITY, and a varchar(max) field for the list of REPS

You insert the data into the main table using the query as above, except don't fill the REP field, and don't fill the IDENTITY field, that popultaes automatically:

INSERT #Temp1
SELECT DISTINCT quote_number, location, model, product, job_title
FROM quote_info
WHERE job_title = 'sales_rep'

next yoy loop through #Temp1 using the IDENTITY field as a counter, and update #Temp1 with concantentated data from a subquery to return the REPS:

DECLARE @Row int
, @RowCount int
SET @Row = 1
SET @RowCount = (SELECT COUNT(1) FROM FROM quote_info WHERE job_title = 'sales_rep')
-- note you have to make sure this count is the same as the count of records for #temp1

WHILE @Row <= @RowCount
  BEGIN
     INSERT #Temp2
     SELECT QNumID, Rep
     FROM #Temp1


  END


0
Jon_RaymondCommented:
Sorry, didn't fimish:

WHILE @Row <= @RowCount
  BEGIN
     INSERT #Temp2
     SELECT QNumID, Rep
     FROM #Temp1

    SEt @Row=@Row+1
  END

--Then loop again
-- some of this is pseudo code

SET @Row=1
WHILE @Row <= @RowCount
  BEGIN
     
     UPDATE t1
     SET rep=t2.rep + ','
   
    FROM #Temp1 t1
    JOIN #temp2 t2 On -- ID fields
    WHERE -- IDs fields = @row for #temp1 and @row for @tem2


    SEt @Row=@Row+1
  END

--Don't have to to finish - and it gets tricky - but this is an approach

Jon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.