如何从多行显示单行？ [英] How to display a Single row from Multiple Rows ?

问题描述

大家好，



i在表格中有数据。

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

如何以上述格式显示数据？



任何人都知道回复此内容....





问候

Nanda Kishore.CH

How to display data as above format ?

Could any one know reply to this....


Regards
Nanda Kishore.CH

推荐答案

试试这个...

Create Table #TableName (Sno Int, Company Nvarchar(40), Location Nvarchar(100))

Insert into #TableName 
Select 1,'A','Chennai' Union All
Select 2,'A','Pune' Union All
Select 3,'B','Delhi' Union All
Select 4,'A','Bangalore' Union All
Select 5,'B','Hyderabad' 

Select Distinct t.Company,STUFF((Select Distinct','+ a.Location 
                                 From #TableName a 
                                 Where a.Company=t.Company 
                                 for Xml Path(''), TYPE
                                ).value('.','VARCHAR(Max)'),1, 1, '') 
                           as Location
From #TableName t

Drop Table #TableName 

输出：

Output:

Company   Location
-------  ------------
A	 Bangalore,Chennai,Pune
B	 Delhi,Hyderabad

这篇关于如何从多行显示单行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！