July 30, 2010 at 7:58 am
I've been a member of this site off and on for a long time and I don't think I've ever posted a question (although maybe I should have at times) but I don't know how to phrase this question to find the answer I'm looking for.
I've got 2 tables, CustomerOrders and CustomerOrderItems. I've made a simplified example with data as follows:
CREATE TABLE [dbo].[TestCustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[OrderDate] [datetime] NOT NULL,
CONSTRAINT [PK_TestCustomerOrders] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TestCustomerOrderItems](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TestCustomerOrdersID] [int] NOT NULL,
[ProductName] [nvarchar](50) NOT NULL,
[Quantity] [int] NOT NULL,
CONSTRAINT [PK_TCustomerOrderItems] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestCustomerOrderItems] WITH CHECK ADD CONSTRAINT [FK_TestCustomerOrderItems_TestCustomerOrders] FOREIGN KEY([TestCustomerOrdersID])
REFERENCES [dbo].[TestCustomerOrders] ([ID])
GO
ALTER TABLE [dbo].[TestCustomerOrderItems] CHECK CONSTRAINT [FK_TestCustomerOrderItems_TestCustomerOrders]
GO
INSERT TestCustomerOrders (CustomerName,OrderDate) VALUES ('John Doe','7/1/2010')
INSERT TestCustomerOrderItems (TestCustomerOrdersID,ProductName,Quantity) VALUES (@@IDENTITY,'Lawn Darts',1)
INSERT TestCustomerOrders (CustomerName,OrderDate) VALUES ('Jane Doe','7/1/2010')
INSERT TestCustomerOrderItems (TestCustomerOrdersID,ProductName,Quantity) VALUES (@@IDENTITY,'Frisbee',1)
INSERT TestCustomerOrders (CustomerName,OrderDate) VALUES ('Jane Doe','7/1/2010')
Declare @Ident [int]
SELECT @IDENT = @@IDENTITY
INSERT TestCustomerOrderItems (TestCustomerOrdersID,ProductName,Quantity) VALUES (@ident,'BasketBall',1)
INSERT TestCustomerOrderItems (TestCustomerOrdersID,ProductName,Quantity) VALUES (@ident,'Baseball',1)
INSERT TestCustomerOrderItems (TestCustomerOrdersID,ProductName,Quantity) VALUES (@ident,'Soccor Ball',1)
I am looking for a query that will return a row like this:
ID CustomerName OrderDate Items
3 Jane Doe 2010-07-01 Basketball,Baseball,Soccor Ball
I just can't get my head around the idea of how to get the query to do this in TSql. This query is for a display and I'd like the overhead to be as low as is possible, although that's not the main point of my question. The main point of my question is how do I get a response like this.
Thanks in advance.
July 30, 2010 at 8:07 am
Are there any flaws on the data provided, sir? whats the relation between [TestCustomerOrderItems] and [TestCustomerOrders] ??
Is [TestCustomerOrders].[ID] = [TestCustomerOrderItems] .[TestCustomerOrdersID] ??
July 30, 2010 at 8:09 am
Yes, there is a relationship (it is in the example, sorry I didn't mention it).
July 30, 2010 at 8:13 am
Will this help you, sire?
WITH CTE AS
(
SELECT Cust.ID ,Cust.CustomerName , Cust.OrderDate , Items.ProductName , Items.Quantity
FROM TestCustomerOrders Cust
JOIN TestCustomerOrderItems Items
ON Cust.ID = Items.TestCustomerOrdersID
)
SELECT p1.id ,CustomerName , OrderDate,
STUFF ( ( SELECT ','+ ProductName
FROM CTE p2
WHERE p2.id = p1.id
ORDER BY ID
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
FROM CTE p1
GROUP BY p1.id ,CustomerName , OrderDate ;
July 30, 2010 at 8:21 am
Wow... fast response. Thank you.
I've been working with sql for a while, but I don't understand that query. I posted it in a query window and get an error when I run it. Intellisense says "The mulitpart identifier <fieldname> can not be bound". I'm not familar with the "WITH" command. Is that generally used inside a stored proc or should I be able to cut and paste that and have it run?
July 30, 2010 at 8:26 am
I'm a dope...
YES! That works. I'm reading BOL about Common Table Expressions. I've never used them before. What is the general purpose of a CTE? Sometimes BOL is not very descriptive.
July 30, 2010 at 8:28 am
BTW, I've never used STUFF either. I'm reading up.
Now I just have to try to translate this to my real data which has many more relationships. I don't just have ProductName, I have an ID to a Products table, etc....
Thank you again.
July 30, 2010 at 1:40 pm
tom 29037 (7/30/2010)
I'm a dope...YES! That works. I'm reading BOL about Common Table Expressions. I've never used them before. What is the general purpose of a CTE? Sometimes BOL is not very descriptive.
CTE's are basically derived tables with additional flexibility. If you're looking for a good overview I would suggest reading this article (and obviously there are many more out there.)
July 30, 2010 at 2:04 pm
Thanks. I went to Books Online and read the specs, and then I came back to this site and looked for some articles on where and when they can be used. I didn't even know they existed, but I see a few places where I could definitely have used them. Mostly Stored Procedures where I've used temp table for aggregated data.
I saw they were added in SQL 2005, so they are relatively new.
Thanks again to everyone.
August 2, 2010 at 1:44 pm
SSC Eights,
Great response.
Do you mind elaborating more on the:
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
string of your solution ?
Thanks.
August 2, 2010 at 2:30 pm
diegocroitoru (8/2/2010)
SSC Eights,Great response.
Do you mind elaborating more on the:
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
string of your solution ?
Thanks.
Take a look at this MSDN entry.
August 2, 2010 at 3:53 pm
diegocroitoru (8/2/2010)
SSC Eights,Great response.
Do you mind elaborating more on the:
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
string of your solution ?
Thanks.
Actually, you need to look at a little bit more of it:
SELECT p1.id ,CustomerName , OrderDate,
STUFF ( ( SELECT ','+ ProductName
FROM CTE p2
WHERE p2.id = p1.id
ORDER BY ID
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
Part 1: SELECT statement
Part 2: STUFF function returning a field.
Part 3: Subquery using FOR XML PATH(''),TYPE
Going from back to front:
Part 3: This correlated subquery builds a comma-delimited list of ProductNames for the id in the parent query.
: FOR XML PATH(''),TYPE says to build that query as an XML datatype, with no node names.
: the .value('.', 'VARCHAR(MAX)') says to return that XML as a varchar(max) field. This allows you to return data that would otherwise be converted to XML tags (i.e. an "&" would be replaced with "& amp;", a "<" would be replaced with "& lt;", etc.) as a string. (Technically, this would be part 4, but it's easier to explain it here.
Part 2: Stuff function starts at the first character (leading comma), and replaces one character with an empty string (in effect, removes the leading space from the comma-delimited string). Returns this string as the column Concat_Values.
Part 1: Return all of this data grouped by appropriate columns.
Edit: put a space after the "&" so it wouldn't show as the symbol - just realize that the space isn't there.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 2, 2010 at 8:35 pm
Wayne, awesome job in that explanation , mate! Thanks.. Hope the OP now and the other poster got what the code exactly does.
Also i remember a thread where RBarryYoung takes a dig at explaining this; I lost that thread details.. that was one awesome long thread analysing in great depths on types of row concatenation methods..
Once again, thanks for lucid explanation!
August 2, 2010 at 9:10 pm
I have to admit that I don't quite get his explaination, but I also haven't got a chance to play with that code yet. I'll be using the example on my real and much more complex tables, so I'm sure it will be a bit of a challege for me to incorporate this solution.
A little side story... I work in a very small business and was explaining to a non-technical co-worker how I was going about seeing if I could find a solution for my problem. He first question was "Will someone just give you the answer?" I told him that yes, someone would help be but there were limits and rules about how you went about getting help. Then I showed him a post of some bonehead who basically asked for a complete solution, and then got bashed (appropriately so) for trying to get someone else to do his work.
I recieved responses to my questions within 2 hours and my co-worker was surprised.
So... point of all that is this: Thank you for a place to ask an honest question and get an honest answer. I'd rate this thread an 11 out of 10. Thanks to everyone!!!
August 3, 2010 at 6:55 am
ColdCoffee (8/2/2010)
Wayne, awesome job in that explanation , mate! Thanks.. Hope the OP now and the other poster got what the code exactly does.Also i remember a thread where RBarryYoung takes a dig at explaining this; I lost that thread details.. that was one awesome long thread analysing in great depths on types of row concatenation methods..
Once again, thanks for lucid explanation!
Thanks. I've found that once you do understand what it's doing, then it all makes sense. And, most importantly, you can explain how it works to others.
Which brings up a pet peeve of mine... DO NOT just copy code from here (ANYWHERE), and put it into your systems without understanding what it does. If you don't understand it, ask.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply