15,996,337 members
See more:
Hi All

I have a table of the following:

FileKey Title FirstName MiddleName LastName
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

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

## Solution 1

Hi,

SQL
`select name + '' + lastname from emplyee`

## Solution 2

Try This.

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

## Solution 3

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
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.

## Solution 5

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

## Solution 6

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.

v2