1

Please help me with the below scenarios.

I have 3 tables where i need to display a single record for one fileno with multiple values of same field should be displayed with delimiters by join all three tables.

OrdersTable (orderguid is Primary key ) and guids are referencing in buyer and property tables I have 2 address referring same fileno I have 2 BuyerName referring same fileno

so the o/p should be fileno address1,address2 zip state buyername,buyername

Orders Table 

Orderguid  (PK) fileno          
8C25-00F768 100         
2C25-00D695 200

Property Table 

Address Zip State   OrderGuid(FK)   
Street no 1 122235  TX  8C25-00F768 
Street no 2 122235  TX  8C25-00F768

Buyers Table 

BuyerName   OrderGuid (FK)          
Dennis Richard  8C25-00F768         
Levis Richard   8C25-00F768

output : 

fileno  Address Zip State   BuyerName
100 Street no 1 ; Street no 2   122235  TX  Dennis Richard; Levis Richard
200 null    null    null    null
jarlh
  • 42,561
  • 8
  • 45
  • 63
swapna s
  • 43
  • 5
  • LEFT JOIN, GROUP BY, STRING_AGG(). – jarlh Sep 06 '19 at 12:13
  • What is your SQLServer version? It affects whether we can recommend string_agg or whether it has to be a stuff/for xml path or similar ugliness – Caius Jard Sep 06 '19 at 12:22
  • Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Amira Bedhiafi Sep 06 '19 at 12:24

1 Answers1

1

Your query should be like this :

EDIT : ( I highly recommend to avoid merging multiple values into a single row) 

    ;WITH CTE AS (



select o.fileno ,
stuff((SELECT ';' + pr.Address from Property pr
inner join Orders o on o.Orderguid=p.Orderguid

 FOR XML PATH ('')), 1, 1, ''
) AS Address

,
stuff((SELECT ';' + b.BuyerName from Buyer b
inner join Orders o1 on o1.Orderguid=p.Orderguid

 FOR XML PATH ('')), 1, 1, ''
) AS Buyers

,p.Zip
,p.State

 ,  ROW_NUMBER() OVER (PARTITION BY o.Orderguid ORDER BY o.Orderguid ) rn


from Orders o 
left join Property p ON p.Orderguid=o.Orderguid 
left join Buyer b ON b.Orderguid=p.Orderguid

group by o.fileno, p.Address, p.Zip, p.State,p.Orderguid,o.Orderguid )
select * from CTE
where rn=1

See results from here

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60