将多行连接成一列 [英] Concatenate multiple rows into one column

问题描述

大家好



我有以下表格：



FileKey Title FirstName MiddleName LastName

1 Tim Adikari先生

2 Sue Andrew女士

3 Mathew William Anyalai先生

3 Elaine June Blazejczyk女士

4 Scott Alfred Bond先生

4 Tony Bromwich先生





我需要根据filekey栏产生以下结果



1-Tim Adikari

2-Sue Andrew

3-Mathew William Anyalai，Elaine June Balzejczyk

4-Scott Alfred Bond，Tony Bromwich





我怎么能这样做？任何人帮助



谢谢

Nasif

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

推荐答案

Hi,

select name + '' + lastname from emplyee

试试这个。



SELECT CONVERT（VARCHAR（5），FileKey）+' - '+ Title +''+ FirstName +''+ MiddleName + ''LastName

FROM YourTableName

Try This.

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

查看此

See this

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.

这篇关于将多行连接成一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！