How to display a Single row from Multiple Rows ? [Resolved]

Posted by Nandkishorre under Sql Server on 9/2/2013 | Points: 10 | Views : 878 | Status : [Member] | Replies : 7
Hi to All,

i have data in a table.

S.No Company Location
1 A Chennai

2 A Pune

3 B Delhi

4 A Bangalore

5 B Hyderabad

I need to display data below format.

Company Location
A Chennai,Pune,Banglore

B Delhi,Hyderabad


How to display data as above format ?

Could any one know reply to this....


Regards
Nanda Kishore.CH




Responses

Posted by: Bandi on: 9/2/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

SELECT t.Company, STUFF((SELECT ',' + s.Location FROM TableName s WHERE s.Company = t.Company FOR XML PATH('')),1,1,'') AS CSV

FROM TableName AS t
GROUP BY t.Company


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/2/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
-- Alternate approach for getting comma separated values
SELECT Company, STUFF(MAX(locations), 1, 1, '') AS locations

FROM TableName AS extern
CROSS APPLY
(
SELECT ','+Location
FROM TableName AS intern
WHERE extern.Company = intern.Company
FOR XML PATH('')
) pre_trimmed (locations)
GROUP BY Company;


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nandkishorre on: 9/2/2013 [Member] Starter | Points: 25

Up
0
Down
Thank u its working.. is there any other way to get answer like this... ?

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nandkishorre on: 9/2/2013 [Member] Starter | Points: 25

Up
0
Down
May i know what type of query is this... i mean what it is called...?

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 9/2/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
select Company,Substring((select(', '+ Location) from test t where test. Company= t. Company order by Company, Location for xml path('')),3,1000)as Location from test group by Company


If this post helps you mark it as answer
Thanks

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 9/2/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
select Company,Substring((select(', '+ Location) from test t where test. Company= t. Company  for xml path('')),3,1000)as Location from test group by Company


If this post helps you mark it as answer
Thanks

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/2/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
>> May i know what type of query is this... i mean what it is called...?
By using FOR XML PATH() and inline query (or CROSS APPLY) we were getting CSV result....

There are other approaches to get comma separated values from a column where as FOR XML PATH() solution is the best solution in terms of code and performance....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response