使用sql server在单行中获取多行数据

本文介绍了使用sql server在单行中获取多行数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 你好所有 i我在asp.net创建了一个酒店管理项目，我在所有城市都有很多分店，酒店名称也相同。 /> 现在我正在创建一个表格，我选择了HOTEL_NAME和CITIES 就像HOTEL NAME是pigion而我有一些城市如QQYYZZ i写了一个查询选择HOTEL_NAME，来自HOTEL的CITIES; 它返回的数据如下：hello alli 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 CITIESlike 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          CITIESPigion                     QQPigion                     YYPigion                     ZZ 现在我想在单ROW中输出OUTPUT ：now i want the OUTPUT in single ROW like:HOTEL_NAME                CITIESPigion                          QQ,YY,ZZ 我可以证明这一点在我的项目中，我使用DataList显示所有 的记录。 请帮帮我。for that i can show this into my project in a single line i am using DataList for showing allthe records.Please help me.推荐答案这可能会有所帮助This might be helpfulDECLARE @CITIES VARCHAR(8000)SELECT @CITIES = COALESCE(@CITIES + ', ', '') + CITIES FROM HotelsSELECT distinct HOTEL_NAME, @CITIES AS CITIES FROM Hotels嗨杰克逊， 试试这个.....Hi Jackson,Try This.....-- Table CreationIF OBJECT_ID('TempDB..#HotelDtls') IS NOT NULL DROP TABLE #HotelDtlsCREATE TABLE #HotelDtls(HotelName VARCHAR(50), CityName VARCHAR(100))-- Sample DataINSERT INTO #HotelDtls(HotelName, CityName) VALUES('Pigion','QQ'),('Pigion','YY'), ('Pigion','ZZ')-- Actual DataSELECT HotelName, CityName FROM #HotelDtls-- Required OutputSELECT HotelName, REPLACE((SELECT CityName AS [data()] FROM #HotelDtls WHERE b.HotelName=HotelName ORDER BY CityName FOR XML PATH('')), ' ', ', ') 'CityName'FROM #HotelDtls bGROUP BY HotelName 问候， GVPrabuRegards,GVPrabu 这篇关于使用sql server在单行中获取多行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！