concatenate values from table join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

concatenate values from table join

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-04-13 : 09:48:51

I have three table: Sales, Orders and Notes. My Sales table contains items that my customers have ordered, the Orders table contains items I have ordered from my suppliers and the Notes table contains miscellaneous comments regarding my ordered items. I'd like to get a result set that shows all items and their quantities ordered by my customers and ordered by me and then I'd like to show all related notes and dates in one additional column.
Here's my table design:



CREATE TABLE [dbo].[Sales](
	[Number] [varchar](50) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Qty] [int] NOT NULL
) 

INSERT INTO [Sales]
           ([Number]
           ,[Name]
           ,[Qty])
VALUES
           ('A123','SCREW 1/3', 3),
           ('A123','SCREW 1/3', 1),
           ('B323','SCREW 1/2', 1)
           
CREATE TABLE [dbo].[Orders](
	[OrderNumber] [varchar](50) NOT NULL,
	[Number] [varchar](50) NOT NULL,
	[Date] [datetime] NOT NULL,
	[Qty] [int] NOT NULL
) 

INSERT INTO [Orders]
           ([OrderNumber]
           ,[Number]
           ,[Date]
           ,[Qty])
     VALUES
           ('O-001', 'A123', '2011-04-01', 2),
           ('O-003', 'A123', '2011-03-21', 4),
           ('O-005', 'B323', '2011-03-21', 1)


CREATE TABLE [dbo].[Notes](
	[OrderNumber] [varchar](50) NOT NULL,
	[Notes] [varchar](200) NOT NULL
) 
INSERT INTO [Notes]
           ([OrderNumber]
           ,[Notes])
     VALUES
           ('O-001','expect delays'),
           ('O-001','removed items 221'),
           ('O-003','by train'),
           ('O-003','extra fuel charge')

The result set that I would like to see should look like this:

 
Number	Name	      SQty    OQty   Dates_Notes
B323	SCREW 1/2	1	1    NULL 
A123	SCREW 1/3	16	24   2011-04-01:expect delays, removed items 221;2011-03-21: extra fuel charge

I started the query like this:


SELECT s.Number, s.Name, SUM(s.Qty) AS SQty, SUM(o.Qty) AS OQty, <Concatenate o.Date & n.Notes>
FROM Sales s
LEFT JOIN Orders o ON s.Number = o.Number
LEFT JOIN Notes n ON o.OrderNumber = n.OrderNumber
GROUP BY s.Number, s.Name

but I don't really know how to approach the concatenate part. Your help is appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-13 : 10:31:56

for example
SELECT s.Number,
s.Name,
SUM(s.Qty) AS SQty,
SUM(o.Qty) AS OQty,
convert(varchar(10),o.Date,102)+': '+ n.Notes as Dates_Notes
FROM Sales s
LEFT JOIN Orders o ON s.Number = o.Number
LEFT JOIN Notes n ON o.OrderNumber = n.OrderNumber
GROUP BY s.Number, s.Name

edit: typo

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-13 : 10:48:23

Close but no smokes with this one. I think I need to concatenate the stuff inside the APPLY, but then I lose the links?
Where's Visakh?!

Jim



SELECT  DISTINCT

s.Number, s.Name
, SUM(s.Qty) OVER(partition by s.Number, s.Name) AS SQty
, SUM(o.Qty) OVER(partition by s.Number, s.Name) AS OQty 
,CONVERT(varchar(10), o.[Date],121)  + ': '+n.Note
FROM #Sales s
INNER JOIN #Orders o ON s.Number = o.Number
OUTER APPLY 
(
SELECT DISTINCT	  
		STUFF((SELECT ':' + s2.Notes
               FROM #notes AS s2 
              -- WHERE s2.OrderNumber = s1.OrderNumber 
                
               FOR XML PATH('')
               ), 1, 1, ''
             ) AS Note
FROM		#notes AS s1
WHERE o.ordernumber = s1.ordernumber 
 )n

Everyday I learn something that somebody else already knew

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-13 : 11:46:35

could you do the concatenation in a cte first then do the rest?


;with cte([OrderNumber], Note) as
(
SELECT [OrderNumber],	  
		STUFF((SELECT cast([Date] as nvarchar(25)) +  ':' + s2.Notes
               FROM notes AS s2 
              inner join [Orders] ord
                on s2.OrderNumber = ord.OrderNumber 
			WHERe  s2.OrderNumber = s1.OrderNumber 
                
               FOR XML PATH('')
               ), 1, 1, ''
             ) AS Note
       FROM notes AS s1

)
select * from cte

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources