CODEWITHSUNDEEP
c#sqlsql-server
Mehran
Mehran

Reputation: 351

how to join two tables into a new table for display in c#?

I have two tables (Flights details) , (Dates of flights)

Flights

id  |    callsign   |    type
1   |     BAW240    |    A370
2   |     AAL767    |    B777
3   |     JTE201    |    A320

Dates

ID  |       date
1   |     10/10/2016
1   |     10/12/2016
1   |     10/14/2016
1   |     10/15/2016
2   |     11/25/2016
2   |     11/29/2016
3   |     10/20/2016
3   |     10/21/2016
3   |     10/22/2016

I want to join two tables like this

id  |    callsign   |    type   | dates
1   |     BAW240    |    A370   | 10/10/2016 , 10/12/2016 , 10/14/2016 , 10/15/2016
2   |     AAL767    |    B777   | 11/25/2016 , 11/29/2016 
3   |     JTE201    |    A320   | 10/20/2016 , 10/21/2016 , 10/22/2016

this final table is just for preview , and letting the user see a summary of dates.
how can i achieve this?

Upvotes: 1

Views: 70

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Select A.*
      ,Dates=B.Dates
 From  Flights A
 Cross Apply (Select Dates=Stuff((Select Distinct ',' + cast(Date as varchar(25))
               From  Dates 
               Where ID=A.ID 
                For XML Path ('')),1,1,'') 
               ) B

Returns

id  callsign    type    Dates
1   BAW240      A370    2016-10-10,2016-10-12,2016-10-14,2016-10-15
2   AAL767      B777    2016-11-25,2016-11-29
3   JTE201      A320    2016-10-20,2016-10-21,2016-10-22

Upvotes: 2

Srdjan
Srdjan

Reputation: 108

I used many times this technique in order to list multiple invoices associated to a receipt.

USE AdventureWorks2008R2
SELECT CAT.id, CAT.callsign, CAT.type,
            STUFF((SELECT ',' + SUB.dates AS [text()]
                        — Add a comma (,) before each value
                        FROM Dates SUB
                        WHERE
                        SUB.flightid= CAT.id
                        FOR XML PATH('') — Select it as XML
                        ), 1, 1, '' )
                        — This is done to remove the first character (,)
                        — from the result
            AS dates
FROM  Flights CAT

Source: https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

Upvotes: 2

Related Questions