[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 717
  • Last Modified:

Concatenate Column Values from Multiple Rows into a Single Column with SQL Server

I would like to combine a field's values from multiple records in a single field. For example:

Last     First     Code
    -------  --------- ----
    Lesand   Danny       1
    Lesand   Danny       2
    Lesand   Danny       3
    Benedi   Eric        7
    Benedi   Eric       14

Result should look like:

    Last     First     Codes
    -------  --------- -----
    Lesand   Danny     1,2,3
    Benedi   Eric       7,14
0
K_CH_Krishna
Asked:
K_CH_Krishna
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
Just have a try and let me know

Replace 'YourTable' which I gave for table name with your actual table name

Raj
SELECT DISTINCT Last, First, 
  NULLIF(   
    STUFF(   
      (SELECT ',' + Code FROM YourTable WHERE Last = D.Last AND First = D.First FOR XML PATH('')  
                        ), 1, 2, ''   
    ), ''   
  ) AS Codes 
FROM YourTable D

Open in new window

0
 
halfbloodprinceCommented:
THIS WORKS!!!!:
First Create this function :
Create Function ufnStringValues (@LastName Varchar(50))
Returns Varchar(100)
As Begin
DECLARE @listStr VARCHAR(100)
Select @listStr = COALESCE(@listStr+',' , '') + Code
FROM Test01
Where Last = @LastName
Return @listStr
End

Then use the above function like this:

Select Last,
         First,
         (
                  Select dbo.ufnStringValues(Last)
            ) As Code
From   Test01  
Group By Last,
            First

0
 
K_CH_KrishnaAuthor Commented:
Wrongly placed
0
 
Éric MoreauSenior .Net ConsultantCommented:
how did you solved it? by using our ideas?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now