SQLServer: 将多行组合为一行

小开

最佳答案

There are several methods.

If you want just the consolidated string value returned, this is a good quick and easy approach

DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + ', ', '') + stringvalue
FROM jira.customfieldValue
WHERE customfield = 12534
AND ISSUE = 19602


SELECT @combinedString as StringValue

Which will return your combined string.

You can also try one of the XML methods e.g.

SELECT DISTINCT Issue, Customfield, StringValues
FROM Jira.customfieldvalue v1
CROSS APPLY ( SELECT StringValues + ','
FROM jira.customfieldvalue v2
WHERE v2.Customfield = v1.Customfield
AND v2.Issue = v1.issue
ORDER BY ID
FOR XML PATH('') )  D ( StringValues )
WHERE customfield = 12534
AND ISSUE = 19602

小开

There's a convenient method for this in MySql called GROUP_CONCAT. An equivalent for SQL Server doesn't exist, but you can write your own using the SQLCLR. Luckily someone already did that for you.

Your query then turns into this (which btw is a much nicer syntax):

SELECT CUSTOMFIELD, ISSUE, dbo.GROUP_CONCAT(STRINGVALUE)
FROM Jira.customfieldvalue
WHERE CUSTOMFIELD = 12534 AND ISSUE = 19602
GROUP BY CUSTOMFIELD, ISSUE

But please note that this method is good for at the most 100 rows within a group. Beyond that, you'll have major performance problems. SQLCLR aggregates have to serialize any intermediate results and that quickly piles up to quite a lot of work. Keep this in mind!

Interestingly the FOR XML doesn't suffer from the same problem but instead uses that horrendous syntax.

小开

You can achieve this is to combine For XML Path and STUFF as follows:

SELECT (STUFF((
SELECT ', ' + StringValue
FROM Jira.customfieldvalue
WHERE CUSTOMFIELD = 12534
AND ISSUE = 19602
FOR XML PATH('')
), 1, 2, '')
) AS StringValue

小开

Using MySQL inbuilt function group_concat() will be a good choice for getting the desired result. The syntax will be -

SELECT group_concat(STRINGVALUE)
FROM Jira.customfieldvalue
WHERE CUSTOMFIELD = 12534
AND ISSUE = 19602

Before you execute the above command make sure you increase the size of group_concat_max_len else the the whole output may not fit in that cell.

To set the value of group_concat_max_len, execute the below command-

SET group_concat_max_len = 50000;

You can change the value 50000 accordingly, you increase it to a higher value as required.

小开

I believe for databases which support listagg function, you can do:

select id, issue, customfield, parentkey, listagg(stingvalue, ',') within group (order by id)
from jira.customfieldvalue
where customfield = 12534 and issue = 19602
group by id, issue, customfield, parentkey

小开

CREATE VIEW  [dbo].[ret_vwSalariedForReport]
AS
WITH temp1 AS (SELECT
salaried.*,
operationalUnits.Title as OperationalUnitTitle
FROM
ret_vwSalaried salaried LEFT JOIN
prs_operationalUnitFeatures operationalUnitFeatures on salaried.[Guid] = operationalUnitFeatures.[FeatureGuid] LEFT JOIN
prs_operationalUnits operationalUnits ON operationalUnits.id = operationalUnitFeatures.OperationalUnitID
),
temp2 AS (SELECT
t2.*,
STUFF ((SELECT ' - ' + t1.OperationalUnitTitle
FROM
temp1 t1
WHERE t1.[ID] = t2.[ID]
For XML PATH('')), 2, 2, '') OperationalUnitTitles from temp1 t2)
SELECT
[Guid],
ID,
Title,
PersonnelNo,
FirstName,
LastName,
FullName,
Active,
SSN,
DeathDate,
SalariedType,
OperationalUnitTitles
FROM
temp2
GROUP BY
[Guid],
ID,
Title,
PersonnelNo,
FirstName,
LastName,
FullName,
Active,
SSN,
DeathDate,
SalariedType,
OperationalUnitTitles

小开

declare @maxColumnCount int=0;
declare @Query varchar(max)='';
declare @DynamicColumnName nvarchar(MAX)='';


-- table type variable that store all values of column row no
DECLARE @TotalRows TABLE( row_count int)
INSERT INTO @TotalRows (row_count)
SELECT (ROW_NUMBER() OVER(PARTITION BY InvoiceNo order by InvoiceNo Desc)) as row_no FROM tblExportPartProforma


-- Get the MAX value from @TotalRows table
set @maxColumnCount= (select max(row_count) from @TotalRows)
 

-- loop to create Dynamic max/case and store it into local variable
DECLARE @cnt INT = 1;
WHILE @cnt <= @maxColumnCount
BEGIN
set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= '+cast(@cnt as varchar)+' then InvoiceType end )as InvoiceType'+cast(@cnt as varchar)+''
set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= '+cast(@cnt as varchar)+' then BankRefno end )as BankRefno'+cast(@cnt as varchar)+''
set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= '+cast(@cnt as varchar)+' then AmountReceived end )as AmountReceived'+cast(@cnt as varchar)+''


set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= '+cast(@cnt as varchar)+' then AmountReceivedDate end )as AmountReceivedDate'+cast(@cnt as varchar)+''




SET @cnt = @cnt + 1;
END;


-- Create dynamic CTE and store it into local variable @query
set @Query='
with CTE_tbl as
(
SELECT InvoiceNo,InvoiceType,BankRefno,AmountReceived,AmountReceivedDate,
ROW_NUMBER() OVER(PARTITION BY InvoiceNo order by InvoiceNo Desc) as row_no
FROM tblExportPartProforma
)
select
InvoiceNo
'+@DynamicColumnName+'
FROM CTE_tbl
group By InvoiceNo'


-- Execute the Query
execute (@Query)

小开

This is an old question, but as of the release of Microsoft SQL Server 2017 you can now use the STRING_AGG() function which is much like the GROUP_CONCAT function in MySQL.

STRING_AGG (Transact-SQL) Documentation

Example

USE AdventureWorks2016
GO
SELECT STRING_AGG (CONVERT(NVARCHAR(max),FirstName), ',') AS csv
FROM Person.Person;

Returns

Syed,Catherine,Kim,Kim,Kim,Hazem