使用sql server在单行中获取多行数据 [英] fetching multiple row data in single row using sql server

问题描述

你好所有



i我在asp.net创建了一个酒店管理项目，我在所有城市都有很多分店，酒店名称也相同。 />
现在我正在创建一个表格，我选择了HOTEL_NAME和CITIES

就像HOTEL NAME是pigion而我有一些城市如QQYYZZ< br $>


i写了一个查询选择HOTEL_NAME，来自HOTEL的CITIES;

它返回的数据如下：

hello all

i am creating a project of Hotel management in asp.net in which i have many branches in all cities and name of hotel is same.
now i am creating a table where i am selecting HOTEL_NAME and CITIES
like HOTEL NAME is "pigion" and i have some cities like "QQ" "YY" "ZZ"

i wrote a query as " Select HOTEL_NAME,CITIES from HOTEL ;
it return data like:

HOTEL_NAME          CITIES
Pigion                        QQ  
Pigion                        YY      
Pigion                                ZZ 

现在我想在单ROW中输出OUTPUT ：

now i want the OUTPUT in single ROW like:

HOTEL_NAME                CITIES
Pigion                              QQ,YY,ZZ

我可以证明这一点在我的项目中，我使用DataList显示所有

的记录。



请帮帮我。

for that i can show this into my project in a single line i am using DataList for showing all
the records.

Please help me.

推荐答案

这可能会有所帮助

This might be helpful

DECLARE @CITIES VARCHAR(8000)  
SELECT @CITIES = COALESCE(@CITIES + ', ', '') + CITIES FROM Hotels

SELECT distinct HOTEL_NAME, @CITIES AS CITIES FROM Hotels

嗨杰克逊，





试试这个.....

Hi Jackson,


Try This.....

-- Table Creation
IF OBJECT_ID('TempDB..#HotelDtls') IS NOT NULL DROP TABLE #HotelDtls
CREATE TABLE #HotelDtls(HotelName VARCHAR(50), CityName VARCHAR(100))
-- Sample Data
INSERT INTO #HotelDtls(HotelName, CityName) VALUES('Pigion','QQ'),('Pigion','YY'), ('Pigion','ZZ')
-- Actual Data
SELECT HotelName, CityName FROM #HotelDtls
-- Required Output
SELECT HotelName,  
  REPLACE((SELECT  CityName AS [data()] 
           FROM #HotelDtls  
           WHERE b.HotelName=HotelName
           ORDER BY CityName FOR XML PATH('')),
          ' ', ', ') 'CityName'
FROM #HotelDtls b 
GROUP BY HotelName

问候，

GVPrabu

Regards,
GVPrabu

这篇关于使用sql server在单行中获取多行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！