Need help with a query

Hi Experts,

I have two tables.

Manu.Part no.               Description                                       Vendor1      Vendor2    Vendor3
HUB SHC1023 1/2 .375-.5     CORD GRIP
Q#15-017-4312                RA Conveyor Extension
Q#15-017-4312 rev2          AOP50 Installation

Manu.Part no.                           VendorID
HUB SHC1023 1/2 .375-.5           19                                                                   
HUB SHC1023 1/2 .375-.5           3                                                                   
HUB SHC1023 1/2 .375-.5           15      

Is there anyway I can join the tables and get the vendors in  vendor1, Vendor2 and Vendor3 of table1

Manu.Part no.               Description                                       Vendor1      Vendor2    Vendor3
HUB SHC1023 1/2 .375-.5     CORD GRIP                                  19                  3                 15
Q#15-017-4312                RA Conveyor Extension
Q#15-017-4312 rev2          AOP50 Installation

Thanks in advance.
Who is Participating?

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

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.

PortletPaulEE Topic AdvisorCommented:
Manu.Part no.               Description                                       Vendor1      Vendor2    Vendor3

That is not a good way to store information; for example what happens if there are 5 vendors?

However, yes it is possible,
but you need to explain why  19 = Vendor1,  3 = Vendor2,  &  15 = Vendor3
what is the logic behind that sequence?
RadhaKrishnaKiJayaAuthor Commented:

Thank you for your reply. Just for example I gave the IDs like that. But I just need the vendors in the same line.
PortletPaulEE Topic AdvisorCommented:
      [Manu.Part no]
    , MAX(table1.Description) as Description
    , MAX(CASE WHEN rn = 1 THEN VendorID END) AS  Vendor1
    , MAX(CASE WHEN rn = 2 THEN VendorID END) AS  Vendor2
    , MAX(CASE WHEN rn = 3 THEN VendorID END) AS  Vendor3
            [Manu.Part no]
          , VendorID
      FROM table2
     ) AS vend
INNER JOIN table1 ON vend.[Manu.Part no] = table1.[Manu.Part no]
      [Manu.Part no]

Open in new window

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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

PortletPaulEE Topic AdvisorCommented:
>>"But I just need the vendors in the same line."
please state the requirements in the question, these facts are important for choosing the best solution (which isn't what I just proposed, now I've seen that comment)

In SQL Server you can use "FOR XML PATH", but I must leave now, and someone else will pick it up.

this is an example of what I mean
, ca1.notes
, o.bonus
FROM [Order] o
                    ', ' + p.[strPaymentMethod] + ':$' + CAST(p.[decPaymentAmount] AS varchar(20))
               FROM [GCM_Payment] AS p
              WHERE p.intOrderID = o.intOrderID
              FOR XML PATH ('')
             , 1, 1, '')
         ) ca1 (Notes)

Open in new window

RadhaKrishnaKiJayaAuthor Commented:
Thank you very much. It worked!!
RadhaKrishnaKiJayaAuthor Commented:
Hi Paul,

Thank you very much for your help. Actually when I tried with the sample it worked but when I am trying to include it in the actual query it is getting too confusing for me.

This is my query right now. There are 3 tables. BOM, Parts and VENDORLINECARD . I am getting ManufacturerPartNo from the Parts table. But want Vendors in the BOM table


  left outer join Parts P  on B.PartID=P.PartID
  left outer join VENDORLINECARD as V on v.ManufacturerPartNo =P.ManufacturerPartNo

  where B.CustomerID=73 and B.JobNum = '15041701  ' and B.BomID = 20 and B.RevisionLvl='1'

After this query mu output is

BomMstID BomID      ManufacturerPartNo            VendorID      OrderDate      CreatedDate      NeedByDate      
1594      20                    Q#15-017-4312 rev2                                                                                        
1595      20                    Q#15-017-4312                                                                                             
1596      20                    HUB SHC1023 1/2 .375-.5          19                                                                                                                                               
1596      20                    HUB SHC1023 1/2 .375-.5           3                                                                                                                                                      
1596      20                    HUB SHC1023 1/2 .375-.5          15      

Please help me how can I add the portion of query you suggested before here.

PortletPaulEE Topic AdvisorCommented:
May I say, it is almost always a bad idea to "make the question simple for us". Much better to use the real table names & colun names and sample data (excluding anything private). Ultimately you get the solution faster.

My guess is along these lines. As this is a much bigger column list I have changed the approach a little by just adjusting the vendor information first, then join to that as a derived table.
    , B.[BomID]
    , P.ManufacturerPartNo
    , v.Vendor1
    , v.Vendor2
    , v.Vendor3
    , B.[OrderDate]
    , B.[CreatedDate]
    , B.[NeedByDate]
    , B.[JobNum]
    , B.[JobDate]
    , B.[PartID]
    , B.[QtyOrdered]
    , B.[PriceCurrent]
    , B.[PricePrevious]
    , B.[SubAssemNum]
    , B.[WearSparePartFlag]
    , B.[PriceTotal]
    , B.[PONum]
    , B.[POCreatedDt]
    , B.[RFQNum]
    , B.[RFQCreatedDt]
    , B.[RFQExpResponseDt]
    , B.[CustomerID]
    , B.[RevisionLvl]
    , B.[QtyRequired]
    , B.[QtyReceived]
    , B.[ExpectedDelDt]
      LEFT OUTER JOIN Parts P ON B.PartID = P.PartID
                            , MAX(CASE WHEN rn = 1 THEN VendorID END) AS Vendor1
                            , MAX(CASE WHEN rn = 2 THEN VendorID END) AS Vendor2
                            , MAX(CASE WHEN rn = 3 THEN VendorID END) AS Vendor3
                        FROM (
                                  , VendorID
                                  , ROW_NUMBER() OVER (PARTITION BY ManufacturerPartNo
                                    ORDER BY (SELECT NULL)) AS rn
                              FROM VENDORLINECARD
                             ) AS vflat
                        GROUP BY ManufacturerPartNo
                      ) AS V ON v.ManufacturerPartNo = P.ManufacturerPartNo
WHERE B.CustomerID = 73
      AND B.JobNum = '15041701  '
      AND B.BomID = 20
      AND B.RevisionLvl = '1'

Open in new window

Please note as the question is finalized if there is much more t do you should open a new question.

why? a new question attracts any expert wanting to answer things in their favoured topics. an already answered question only attracts the persons who contributed initially (and as I'm the only contributor that means you wold be stuck with me only, and I'm not always available).
RadhaKrishnaKiJayaAuthor Commented:
Thank you very much for your help.
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
Visual Basic.NET

From novice to tech pro — start learning today.