Je recherche une solution pour générer le résultat à partir des tables Table1 et Table2. Veuillez voir l'image ci-dessous pour plus de détails

enter image description here

Table1.OrderID = Table2.OrderID

Je ne recherche pas une simple requête de jointure. Dans la sortie Table1 les valeurs ne se répètent pas.

-3
ellickakudy rajeesh 21 avril 2017 à 10:44

3 réponses

Meilleure réponse

Et enfin j'ai une solution pour ce que je recherche exactement

WITH MasterTable as (SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID ASC) AS SlNo,* FROM Table1),
DetailTable as (SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID ASC) AS SlNo,* FROM Table2)

SELECT * FROM MasterTable A FULL JOIN DetailTable B
ON A.OrderID = B.OrderID AND A.SlNo=B.SlNo
ORDER BY B.OrderID
1
jarlh 21 avril 2017 à 09:03

Vous pouvez obtenir ce résultat en utilisant ROW_NUMBER () OVER (PARTITION BY ORDER BY) code:

select 
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY tb1.orderid ORDER BY tb1.orderid) = 1 THEN tb1.orderid ELSE null END AS orderid,
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY tb1.custname ORDER BY tb1.orderid) = 1 THEN tb1.custname ELSE '' END AS custname,
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY tb1.Descp ORDER BY tb1.orderid) = 1 THEN tb1.Descp ELSE '' END AS Descp,
    tb2.item,tb2.quentity
    from tb1 inner join tb2 on tb1.orderid=tb2.orderid

Production :

orderid custname    Descp   item    quentity
   1    Ccustomer1  1item   Pen     1
   2    Ccustomer2  2item   Ball    2
                             Bat    1
   3    Ccustomer3  3item   Book    2
                            Box     1
                            Bag     1
                            Pen     2
0
ITSGuru 21 avril 2017 à 09:37
select Table1.*, Table2.Items, Table2.Quantity -- List the columns you want. I've specified the table name to avoid ambiguous column errors, and * means all columns
from Table1
inner join Table2 -- This is a join (inner gets only the records that exist in both tables)
on Table1.OrderID = Table2.OrderID -- This is the join condition, you define which columns are the same between the tables

Et pour les bits vierges, car vous n'avez pas le sens de gérer cela dans la couche d'affichage:

with CTE as
(
select Table1.*, 
       Table2.Items, 
       Table2.Quantity,
       row_number() over(partition by Table1.OrderID order by Items ) as rn
    from Table1
    inner join Table2 
    on Table1.OrderID = Table2.OrderID 
)

select case when rn = 1 then OrderID end as OrderID,
       case when rn = 1 then CustomerName end as CustomerName ,
       case when rn = 1 then CTE.Desc end as Desc,
       Items,
       Quantity
from CTE
2
JohnHC 21 avril 2017 à 08:43