SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 concatenate values from table join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HenryFulmer
Posting Yak Master

USA
110 Posts

Posted - 04/13/2011 :  09:48:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 04/13/2011 :  10:31:56  Show Profile  Visit webfred's Homepage  Reply with Quote
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.

Edited by - webfred on 04/13/2011 10:32:43
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/13/2011 :  10:48:23  Show Profile  Reply with Quote
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
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 04/13/2011 :  11:46:35  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

Edited by - yosiasz on 04/13/2011 12:01:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000