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

Posted by Nandkishorre under Sql Server on 9/2/2013 | Points: 10 | Views : 1247 | Status : [Member] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	-- 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

-- 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

0	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

0	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

0	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

0	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

0	>> 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

>> 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

Latest Posts