Got it done faster than I thought. So in this example, richmond should have one line but have three origincodes separated by comma. Kiosk should have two origincodes separated by commas and individual is only one origincode.

CREATE TABLE [dbo].[LOC](

[OriginCode] [varchar](40) NOT NULL,

[OCID] [varchar](8) NOT NULL,

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DEST](

[DestID] [varchar](40) NOT NULL,

[OCID] [varchar](8) NOT NULL,

[Ordered] [decimal](8,2) NULL,

) ON [PRIMARY]

GO

INSERT INTO [dbo].[LOC](OriginCode,OCID)

SELECT'Richmond', '804'

UNION ALL

SELECT'Raleigh', '919'

UNION ALL

SELECT 'Roanoke', '540'

UNION ALL

SELECT'Virginia Beach', '757'

INSERT INTO [dbo].[DEST](DestID,OCID,Ordered)

SELECT'Business', '804', 8

UNION ALL

SELECT'Individual', '919', 18

UNION ALL

SELECT 'Kiosk', '540', 64

UNION ALL

SELECT'Business', '757', 12

UNION ALL

SELECT'Business', '919', 36

UNION ALL

SELECT'Kiosk', '757', 41

selectorigincode, destid, sum(ordered) as Total

fromloc l join dest d on l.ocid = d.ocid

group

byorigincode, destid

order

bydestid

I'd like to see output like this in three colums(OriginCode,DestID,Total)

Raleigh, Richmond, Virginia Beach | Business | 56

Roanoke, Virginia Beach | Kiosk | 105

Raleigh | Individual | 18