Appending data in T-SQL

Question

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.

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#

instead of

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

??

check out this "simulation" of a GROUP_CONCAT : http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — Alexis Peters, Dec 18 '14 at 10:00

score 3 · Accepted Answer · answered Dec 18 '14 at 16:24

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)

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.

I put the order by clause after the Split(a) now and it is working but it is generating another error: XML parsing: line 1, character 23, illegal name character — Somdip Dey, Dec 18 '14 at 16:31

score 0 · Answer 2 · answered Dec 18 '14 at 10:00

0

try this:

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]) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a)

answered Dec 18 '14 at 10:00

Dgan

10,077
1
29
51

i am getting the value in dataset from the server through C# programming. But the code above is for xml...isn't it??!! pardon me, I am not very good with sql .... – Somdip Dey Dec 18 '14 at 10:06
@SomdipDey this is for SQL SERVER – Dgan Dec 18 '14 at 10:07
@SomdipDey in C# you can split string and u will get desired op – Dgan Dec 18 '14 at 10:08
hmmm. One more thing, the name, address and booknames are from different tables....so how to approach that? How can I modify the sql above to do that....? – Somdip Dey Dec 18 '14 at 10:11
@SomdipDey yes you can use derived tables or CTE `[table]` will be your derived table/CTE – Dgan Dec 18 '14 at 10:16
since i have never used CTE before, so which table name do i need to provide in [table] ....Customer/Details/Lending?? Could you please help me with this? – Somdip Dey Dec 18 '14 at 10:24
I used the above sql text with an 'order by' cluase such as : SELECT Name, Address , Split.a.value('.', 'VARCHAR(100)') BookName FROM (SELECT Name, Address , Cast ('' + Replace(BookName, ',', '') + '' AS XML) AS Data FROM [table] where ID = '1' order by Name) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) – Somdip Dey Dec 18 '14 at 16:21

score 0 · Answer 3 · edited May 23 '17 at 10:31

0

While I think this is generally a bad idea - returning multiple data items in a single cell - there are a number of ways to go about it, with different performance concerns.

What you're looking for is here: Concatenate many rows into a single text string?

edited May 23 '17 at 10:31

Community

1
1

answered Dec 18 '14 at 10:01

mrmillsy

495
3
14

Appending data in T-SQL

3 Answers3

Linked