Simple Select ? 2

[buddafish]

Hello all,

I am attempting to return a row as such:

[Order Number, Salesman1, Salesman2]

from a table that has 2 columns: Order Number, SalesMan.

Data: OrderNum SalesMan
1234 Bob
1234 Mike

Both Salesmen share the order and i would like to output on one line. I was thinking Select Distinct OrderNum, Salesman... but that does not work

Suggestions?

Thanks in advance
Scott

 

[gmmastros]

Unfortunately, it's not simple, but that won't stop us from helping you.

Take a look here.
thread183-1159740

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

SELECT t1.OrderNum  AS [Order Number]
     , t1.SalesMan  AS Salesman1
     , t2.SalesMan  AS Salesman2
  FROM sales AS t1
INNER
  JOIN sales AS t2
    ON t1.OrderNum = t2.OrderNum
   AND t1.SalesMan < t2.SalesMan

r937.com | rudy.ca

 

[buddafish]

Excellent! Thanks for the help. I knew this could be done but was WAY off on my approach.

thanks again !

 

[ESquared]

Are you sure you'll always have no more than 2 salespeople per order?

 

[buddafish]

Actually there could be anywhere between 1 and 4... still working out some ideas

 

[gmmastros]

First, create this function.

[COLOR=blue]Create[/color] [COLOR=#FF00FF]Function[/color] GetSalesmanList(@OrderNum [COLOR=blue]Int[/color])
Returns [COLOR=blue]VarChar[/color](8000)
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]

  [COLOR=blue]Declare[/color] @Output [COLOR=blue]VarChar[/color](8000)
  [COLOR=blue]Set[/color] @Output = [COLOR=red]''[/color]

  [COLOR=blue]Select[/color] @Output = @Output + SalesMan + [COLOR=red]','[/color]
  [COLOR=blue]From[/color]   [!]YourTable[/!]
  [COLOR=blue]Where[/color]  OrderNum = @OrderNum
         And SalesMan > [COLOR=red]''[/color]

  [COLOR=blue]Set[/color] @Output = [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]Right[/color](@Output,1) = [COLOR=red]','[/color] [COLOR=blue]Then[/color] [COLOR=#FF00FF]Left[/color](@Output, Len(@Output)-1) [COLOR=blue]Else[/color] @Output [COLOR=blue]End[/color]

  [COLOR=blue]Return[/color] @Output
[COLOR=blue]End[/color]

Then, use the function like this....

Select Distinct OrderNum, dbo.GetSalesmanList(OrderNum) As SalesPeople
From   [!]YourTable[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Naqibuquo]

take also a look here:

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables