Concatenate multiple rows into one column

Question

0.00/5 (No votes)

See more:

Hi All

I have a table of the following:

FileKey Title FirstName MiddleName LastName
1 Mr Tim Adikari
2 Ms Sue Andrew
3 Mr Mathew William Anyalai
3 Mrs Elaine June Blazejczyk
4 Mr Scott Alfred Bond
4 Mr Tony Bromwich

I need to produce result of the following based on the filekey column

1-Tim Adikari
2-Sue Andrew
3-Mathew William Anyalai,Elaine June Balzejczyk
4-Scott Alfred Bond,Tony Bromwich

How can I do that? Anyone help

Thanks
Nasif

Posted 4-Jun-13 18:52pm

NASIFKHAN

Add a Solution

5 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rockstar_ · Answer 1 · 2013-06-04T19:15:00

Solution 1

Hi,

SQL

select name + '' + lastname from emplyee

Posted 4-Jun-13 19:15pm

Rockstar_

Arun Vasu · Answer 2 · 2013-06-04T19:24:00

Solution 2

Try This.

SELECT CONVERT(VARCHAR(5),FileKey) + ' - ' + Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName
FROM YourTableName

Posted 4-Jun-13 19:24pm

Arun Vasu

RDBurmon · Answer 3 · 2013-06-04T19:29:00

See this

C#

DECLARE @tbl as table (FileKey nvarchar(10),Title  nvarchar(10),FirstName  nvarchar(10),MiddleName  nvarchar(10),LastName nvarchar(10))
INSERT INTO @tbl 
SELECT '1','Mr','Tim','','Adikari'
UNION
SELECT '2', 'Ms','Sue','','Andrew'
UNION
SELECT '3', 'Mr','Mathew','William','Anyalai'
UNION
SELECT '3', 'Mrs','Elaine','June','Blazejczyk'
UNION
SELECT '4', 'Mr','Scott','Alfred','Bond'
UNION
SELECT '4', 'Mr','Tony','','Bromwich'


SELECT p1.FileKey,
      ( SELECT Title + ' ' + FirstName + ' ' +  MiddleName + ' ' + LastName + ','
          FROM @tbl p2
         WHERE p2.FileKey = p1.FileKey
         ORDER BY FileKey
           FOR XML PATH('') ) AS Products
  FROM @tbl p1
 GROUP BY FileKey ;

Replace

@tbl

in above query with your actual table name.

Umapathi K · Answer 4 · 2013-06-04T19:53:00

with cte
as
(
SELECT * FROM yourtablename
)

SELECT p1.FileKey,
(SELECT Title + '-' + FirstName + ' ' + MiddleName + ' ' + LastName + ','
FROM cte p2
WHERE p2.FileKey = p1.FileKey
ORDER BY FileKey
FOR XML PATH('') ) AS FullName
FROM cte p1
GROUP BY FileKey ;

I have used common table expression

damodara naidu betha · Answer 5 · 2013-06-13T00:56:00

Hi,

Try this

SQL

SELECT (CAST(FileKey AS VARCHAR)+'-'+STUFF(( SELECT ',' + CONCAT(ISNULL(tc.FirstName+' ',''),
ISNULL(tc.MiddleName+' ',''),ISNULL(tc.LastName,''))
FROM YourTable tC WHERE YourTable.FileKey= tc.FileKey
FOR XML PATH('') ),1,1,'')) DesiredValue  FROM YourTable GROUP BY FileKey

Hope .. this will help.

Concatenate multiple rows into one column

5 solutions

Solution 1

Solution 2

Solution 3

Solution 5

Solution 6

Add your solution here

Preview 0

Concatenate multiple rows into one column

5 solutions

Solution 1

Solution 2

Solution 3

Solution 5

Solution 6

Add your solution here

Preview 0

Existing Members

...or Join us