January 15, 2012 at 3:09 pm
I have a table with three columns, storing shoeID, shoe, and color. Together they form a composite PK as each combo can occur only once. A sample of the table is as follows:
shoeIDshoecolor
1t-strapblue
1t-strapred
1t-strapwhite
2clogred
2clogwhite
3tennieblue
3tenniegreen
3tenniewhite
3tennieyellow
I want to write a select query that will retrieve shoe type in one column, and all colors that it comes in, comma seperated, in another column like so:
t-strap blue, red, white
clog red, white
tennie blue, green, white, yellow
How do I write this query? Thank you very much if you can help.
-
January 15, 2012 at 7:53 pm
Note*, I believe the consensus is that cursors and while statements are not preferred. A non cursor based solution would be ideal, for concatenating multiple rows of colors into one column, per shoe.
-
January 16, 2012 at 1:20 am
aitchkcandoo (1/15/2012)
Note*, I believe the consensus is that cursors and while statements are not preferred. A non cursor based solution would be ideal, for concatenating multiple rows of colors into one column, per shoe.
You'll get a faster reply if you provide sample data in the form shown in the solution below. This is not a hard question, but you will have put people off answering because they have to do a lot of work just to set up the data.
Data:
USE Sandpit
DECLARE @Example TABLE
(
ShoeID integer NOT NULL,
Name varchar(20) NOT NULL,
Colour varchar(20) NOT NULL
)
INSERT @Example
(ShoeID, Name, Colour)
VALUES
(1, 't-strap', 'blue'),
(1, 't-strap', 'red'),
(1, 't-strap', 'white'),
(2, 'clog', 'red'),
(2, 'clog', 'white'),
(3, 'tennie', 'blue'),
(3, 'tennie', 'green'),
(3, 'tennie', 'white'),
(3, 'tennie', 'yellow');
Solution:
SELECT
q1.Name,
STUFF(ca.csv.value('./text()[1]', 'varchar(MAX)'), 1, 2, '')
FROM
(
SELECT DISTINCT
e.Name
FROM @Example AS e
) AS q1
CROSS APPLY
(
SELECT
', ' + e2.Colour
FROM @Example AS e2
WHERE
e2.Name = q1.Name
ORDER BY
e2.Colour
FOR XML
PATH (''), TYPE
) AS ca (csv)
January 16, 2012 at 1:43 am
OK. I will do that.
CREATE TABLE [dbo].[ShoeColor](
[shoeID] [int] NOT NULL,
[shoe] [varchar](30) NOT NULL,
[varchar](30) NOT NULL)
INSERT ShoeColor
(ShoeID, Shoe, Color)
VALUES
(1, 't-strap', 'blue'),
(1, 't-strap', 'red'),
(1, 't-strap', 'white'),
(2, 'clog', 'red'),
(2, 'clog', 'white'),
(3, 'tennie', 'blue'),
(3, 'tennie', 'green'),
(3, 'tennie', 'white'),
(3, 'tennie', 'yellow');
I just stumbled on another solution taken from stackoverflow:
(http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server)
SELECT
[shoe]
, STUFF((SELECT ', ' + FROM ShoeColor WHERE (Shoe = Results.shoe) FOR XML PATH ('')),1,2,'') AS NameValues
FROM shoecolor Results
GROUP BY Shoe
Results:
clogred, white
t-strapblue, red, white
tenniered, white
SQL Kiwi, your solution looks more complicated. Why cross apply?
-
January 16, 2012 at 2:08 am
aitchkcandoo (1/16/2012)
OK. I will do that.
Well it's a bit late now 🙂
Remember it for your next question. Better question = betetr, faster answer
I just stumbled on another solution taken from stackoverflow:
(http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server)
Yes it is a very well-known technique. Using a search engine is a good idea before submitting a question.
SQL Kiwi, your solution looks more complicated. Why cross apply?
Because it helps to show the general technique better. See my two-part article on APPLY for more details:
http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/
The other form is a similar idea, with a similar execution plan, but harder to understand for most people who don't know the technique already. Again, please see part 2 of the article above for a detailed discussion of APPLY versus sub-queries.
SELECT
e.Name,
STUFF
(
(
SELECT
', ' + e2.Colour
FROM @Example AS e2
WHERE
e2.Name = e.Name
FOR XML
PATH ('')
),1,2,''
) AS NameValues
FROM @Example AS e
GROUP BY
e.Name
January 16, 2012 at 12:03 pm
SQL Kiwi, I added my create and insert script so that someone else who comes to forum may be able to duplicate solution that I posted, if they were inclined (to compare with yours).
To find an answer, one needs to know how to look for it. In this case, it wasn't until I used 'concatenate' in my search that I found some answers. Prior to that I had been searching, to be sure.
Thank you for your post. I will examine your papers.
-
January 16, 2012 at 12:11 pm
aitchkcandoo (1/16/2012)
SQL Kiwi, I added my create and insert script so that someone else who comes to forum may be able to duplicate solution that I posted, if they were inclined (to compare with yours).
I see, well that makes sense, thanks.
To find an answer, one needs to know how to look for it. In this case, it wasn't until I used 'concatenate' in my search that I found some answers. Prior to that I had been searching, to be sure.
Understood.
January 16, 2012 at 12:13 pm
Have you checked this article[/url] out yet?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 16, 2012 at 5:50 pm
Thanks to both of you. I'm reading up on FOR XML clause, PATH mode, and STUFF function right now to be sure I know how to manipulate returned sets, and then I will move on to the suggested readings.
I wanted to experiment and thought I'd try generating slightly different output such as just one row of data, listing shoe type once.:
t-strap, clog, tennie
I tried:
select stuff((select ', ' + shoe from shoecolor where (shoe = results.shoe) for xml path('')),1,2,'')
from shoecolor results
group by shoe
but I'm getting every row of shoe concatenating into this output:
t-strap, t-strap, t-strap
clog, clog
tennie, tennie
the distinct function didn't help.
How do I tweak the query so that it returns only one instance of shoe type?
-
January 16, 2012 at 7:44 pm
WayneS, that article was dead on, spot on. Thanks.
-
January 17, 2012 at 8:23 am
aitchkcandoo (1/16/2012)
WayneS, that article was dead on, spot on. Thanks.
Thought it might help. Thanks for reading it, and you're quite welcome.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 17, 2012 at 10:15 pm
Would either of you mind helping me with this some more? I am still practicing the FOR XML PATH technique and I want to apply it to a nearly identical situation with the exception that I must join two tables in order to get the values that I'm after. I'm using AdventureWorks.
I want to list all salesorderIDs in the SalesOrderHeader table associated with a customerID in the Sales.Individual table on one row eg:
---this query
use AdventureWorks
go
select i.CustomerID, soh.SalesOrderID
from Sales.individual i
join Sales.SalesOrderHeader soh
on i.customerid = soh.CustomerID;
---yields this output
CustomerIDSalesOrderID
1100043793
1100051522
1100057418
1100143767
1100151493
1100172773
1100243736
1100251238
1100253237
---but I would like output to look like this:
11000 43793, 51522, 57418
11001 43767, 51493, 72773
11002 43736, 51238, 53237
---since there are two tables, I'm selecting into a temp table
use AdventureWorks
go
select i.CustomerID, soh.SalesOrderID into #temp
from Sales.individual i
join Sales.SalesOrderHeader soh
on i.customerid = soh.CustomerID;
---and trying to apply same pattern
use AdventureWorks
go
select customerid, STUFF ((select ', ' + salesorderid
from #temp
where(customerid = results.customerid)for XML path ('')),1,2,'') as salesOrders
from #temp results
group by customerid;
---but I get this error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ', ' to data type int.
Will you please recommend the best way to get the output listed, when joining two tables. Please! thanks in advance.
-
January 17, 2012 at 10:32 pm
cast salesorderid as varchar in subquery
select customerid, STUFF ((select ', ' + CAST(salesorderid AS VARCHAR(50))
from #temp
where(customerid = results.customerid)for XML path ('')),1,2,'') as salesOrders
from #temp results
group by customerid;
Regards,
venugopal.rr
January 18, 2012 at 1:24 am
Here you go:
SELECT
i.CustomerID,
STUFF(
(
SELECT
',' + CONVERT(varchar(12), soh.SalesOrderID) AS [text()]
FROM Sales.SalesOrderHeader AS soh
WHERE
soh.CustomerID = i.CustomerID
ORDER BY
soh.SalesOrderID
FOR XML
PATH (''),
TYPE
).value('./text()[1]', 'VARCHAR(MAX)')
, 1, 1, SPACE(0)
)
FROM Sales.Individual AS i
GROUP BY
i.CustomerID
January 18, 2012 at 1:26 am
Thank you venugopal.rr and Paul. I was thrown by the reference to ','. So, because the salesorderid is being output as XML, it must be cast as varchar datatype, if it is not already. If that is correct, thank you very much for the solution (which works). If that is not the correct explanation, let me know.
Bless you for your help.
-
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply