September 9, 2015 at 12:32 pm
I am trying to get the 3 rows in the below in one line. with all 3 names separated by ","
create table #Product
(
Application Varchar(50),
Name varchar(10),
Counts Int
)
insert into #Product values('Excel', 'Dave', 1)
insert into #Product values('Excel', 'Ken', 10)
insert into #Product values('Excel', '', 4)
insert into #Product values('SQL', 'Tim', 6)
Select * from #Product
However I want to get all 3 rows in 1 but can't get all columns . I can do so only with name column. Please advise
SELECT distinct STUFF((SELECT ',' + Name
FROM #Product A
ORDER BY [Application]
FOR XML PATH('')), 1, 1, '') AS [Output]
September 9, 2015 at 12:40 pm
sharonsql2013 (9/9/2015)
I am trying to get the 3 rows in the below in one line. with all 3 names separated by ","create table #Product
(
Application Varchar(50),
Name varchar(10),
Counts Int
)
insert into #Product values('Excel', 'Dave', 1)
insert into #Product values('Excel', 'Ken', 10)
insert into #Product values('Excel', '', 4)
insert into #Product values('SQL', 'Tim', 6)
Select * from #Product
However I want to get all 3 rows in 1 but can't get all columns . I can do so only with name column. Please advise
SELECT distinct STUFF((SELECT ',' + Name
FROM #Product A
ORDER BY [Application]
FOR XML PATH('')), 1, 1, '') AS [Output]
Not sure what are wanting here. Are you wanting all 3 values shoved into a single column as output? Are you expecting only 1 row as output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 12:42 pm
I want to see output as
Excel Dave,Ken 15
September 9, 2015 at 12:49 pm
sharonsql2013 (9/9/2015)
I want to see output asExcel Dave,Ken 15
Help me here. Is this one column? You need to put a little effort into this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 12:51 pm
3 columns 1 row
September 9, 2015 at 12:54 pm
Do you realize that the STUFF function is not the one concatenating the rows? The stuff function is only replacing the first character (a comma) with an empty string.
Try understanding how this works to be able to troubleshoot it. Wayne Sheffield explains it in this article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Follow the explanation and run the code step by step, trying different things to understand it.
Finally, here's what you might want. Please understand the differences from your original query.
SELECT [Application],
STUFF((SELECT ',' + Name
FROM #Product A
WHERE A.[Application] = p.[Application]
AND A.Name <> ''
FOR XML PATH('')), 1, 1, '') AS Names,
SUM(Counts)
FROM #Product p
GROUP BY [Application]
September 9, 2015 at 12:57 pm
Luis Cazares (9/9/2015)
Do you realize that the STUFF function is not the one concatenating the rows? The stuff function is only replacing the first character (a comma) with an empty string.Try understanding how this works to be able to troubleshoot it. Wayne Sheffield explains it in this article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Follow the explanation and run the code step by step, trying different things to understand it.
Finally, here's what you might want. Please understand the differences from your original query.
SELECT [Application],
STUFF((SELECT ',' + Name
FROM #Product A
WHERE A.[Application] = p.[Application]
AND A.Name <> ''
FOR XML PATH('')), 1, 1, '') AS Names,
SUM(Counts)
FROM #Product p
GROUP BY [Application]
Thanks Luis. I was about to post the exact same query. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 12:59 pm
That's helps. Thanks
September 9, 2015 at 1:01 pm
sharonsql2013 (9/9/2015)
3 columns 1 row
You know that when you don't bother to put in any effort into providing details it ends up coming across as though you want somebody to do your work for you so you don't have to. Nobody asks for details to be annoying, we ask for them so we can provide the query that will actually do what you want. It minimizes the amount of back and forth required. You seem to want and need help here but you seem unwilling to put in much effort on your own to provide the details needed. If you were a paying client I wouldn't mind because that just racks up the billable time. However, the people around here do this for FREE.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 1:03 pm
Sean Lange (9/9/2015)
sharonsql2013 (9/9/2015)
3 columns 1 rowYou know that when you don't bother to put in any effort into providing details it ends up coming across as though you want somebody to do your work for you so you don't have to. Nobody asks for details to be annoying, we ask for them so we can provide the query that will actually do what you want. It minimizes the amount of back and forth required. You seem to want and need help here but you seem unwilling to put in much effort on your own to provide the details needed. If you were a paying client I wouldn't mind because that just racks up the billable time. However, the people around here do this for FREE.
+10 000
September 9, 2015 at 1:21 pm
sharonsql2013 (9/9/2015)
I am trying to get the 3 rows in the below in one line. with all 3 names separated by ","create table #Product
(
Application Varchar(50),
Name varchar(10),
Counts Int
)
insert into #Product values('Excel', 'Dave', 1)
insert into #Product values('Excel', 'Ken', 10)
insert into #Product values('Excel', '', 4)
insert into #Product values('SQL', 'Tim', 6)
Select * from #Product
However I want to get all 3 rows in 1 but can't get all columns . I can do so only with name column. Please advise
SELECT distinct STUFF((SELECT ',' + Name
FROM #Product A
ORDER BY [Application]
FOR XML PATH('')), 1, 1, '') AS [Output]
You provided much of what was needed in your OP (Original Post), but you didn't really show what you expected as a result. Something like this would have helped a lot:
create #ExpectedResults (
Application varchar(50),
UserNames varchar(2000) -- not sure how many 10 character names you may concatenate
);
insert into #ExpectedResults
values ('Excel','Dave, Ken'),('SQL','Tim');
select * from #ExpectedResults;
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply