在T-SQL中附加数据 [英] Appending data in T-SQL

问题描述

我有一个sql(事务sql-SQL Server 2012)，用于从具有有效地址(从表详细信息中)的表(客户)中获取客户名称:

I have a sql (transact sql - SQL server 2012) which used to fetch names of customers from a table (Customer) who has valid addresses (from table Details):

Select Customer.Name, Details.Address 
from Customer 
left outer join Details on Details.Customer = Customer.Name

这通常用于每次从数据库服务器发送回每个客户的每个记录(名称)行.没有提取多条记录.

This used to send back each record (name) row for each customer every time from the db server. No multiple records are fetched.

最近，我需要修改此sql文本，以便甚至根据数据库获取他们借入的书籍的名称，该名称保存在另一个表中(借出).现在脚本如下:

Recently I needed to modify this sql text in order to fetch even the name of the books they have borrowed as per the database, which is saved in another table (Lending). Now the script looks like:

Select Customer.Name, Details.Address, Lending.BookName 
from Customer 
left outer join Details on Details.Customer = Customer.Name 
left outer join Lending on Lending.CustomerName = Customer.Name

它可以正确返回记录，但是现在我遇到了问题.由于客户可以借用多本图书，因此对于同一客户，返回的数据具有多行，其中显示了多本图书名称.根据我的软件规范，我需要为每位客户提取一行，在这一行中，我需要将所有图书名称附加在一个栏中.有人可以帮我吗:如何在单个列中为同一记录附加多个数据，例如:

It is returning the records properly, but now I have got a problem. Since a customer can borrow multiple books, the returned data has multiple rows for the same customer showing multiple book names. According to my software specification I need to fetch one line for each customer and in that one row i need to append all the book names in a single column. Can someone help me with this: How to append multiple data for same record in a single column such as:

Name    Address    BookName
Somdip  XX         Brief History of Time,Headfirst SQL,Headfirst C#

代替

Name    Address    BookName
Somdip  XX         Brief History of Time
Somdip  XX         Headfirst SQL
Somdip  XX         Headfirst C#

??

推荐答案

我将上述sql文本与'where'和'order by'子句一起使用，例如:

I used the above sql text with 'where' and 'order by' clauses such as :

SELECT Name,
       Address    ,
       Split.a.value('.', 'VARCHAR(100)') BookName
FROM   (SELECT Name,
               Address    ,
               Cast ('<M>' + Replace(BookName, ',', '</M><M>') + '</M>' AS XML) AS Data
        FROM   [table] where ID = '1' order by Name) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a)

这给我一个错误:除非也指定了TOP，OFFSET或FOR XML，否则ORDER BY子句在视图，内联函数，派生表，子查询和公用表表达式中无效.

and it is giving me an error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

这篇关于在T-SQL中附加数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！