top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?

+1 vote
96 views
Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?
posted Dec 29, 2014 by Amit Kumar Pandey

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

If you're using SQL Server 2005, you could use the FOR XML PATH command.

SELECT [CustomerID]
     , [Name]
     , (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (CustomerID = Customer.CustomerID) 
         FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM [Customer]

It's a lot easier than using a cursor, and seems to work fairly well.

answer Jan 2, 2015 by Vrije Mani Upadhyay
Similar Questions
+1 vote

Suppose we have a table with the following data:

ClientID    ClientName
3           Sowrabh Malhotra
4           Saji Mon
6           Sajith Kumar
7           Vipin Job
8           Monoj Kumar

We need to concatenate the ClientName column like the following:

Sowrabh Malhotra, Saji Mon, Sajith Kumar, Vipin Job, Monoj Kumar
+1 vote

I have data in table A as below

Assetid   attribute   value
    1546    Ins_date   05062011
    1546    status     active
    1546    X          10.4567
    1546    Y          27.56
    1546    size       17
    675     X          4.778
    675     Y          53.676
    675     depth      5
    675     st_date    06092010

I have data as above in table A. This table has many Assetids 1546,675,....etc. attributes might vary for assets.

I want output as below:

assetid  ins_date  status  X        Y       Size  depth  st_date
1546     05062011  active  10.4567  27.56   17    null   null
675      null      null    4.778    53.676  null  5      06092010

I have created Stored procedure, then called in Informatica to achieve this output. However, since i have large volume of data, it is taking much time to load.

Please suggest me other easy and best way to load it.

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...