I have the following table: tblFile
My Desired output:
I am Concatenating many rows into a single text string; however, I cannot get the grouping correct. As the code is now it will just display for each record in the FileNameString field: AAA,BBB,CCC,DDD,EEE,FFF
Any suggestions with the grouping!
SELECT FileID, Stuff(
(SELECT N', ' + CONVERT(Varchar, FileName)
FROM tblFile FOR XML PATH(''),TYPE )
.value('text()[1]','nvarchar(max)'),1,2,N'')AS FileNameString
From tblFile
GROUP BY FileID
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value.
Concatenate cells if same value with formulas and filter 1. Select a blank cell besides the second column (here we select cell C2), enter formula =IF(A2<>A1,B2,C1 & "," & B2) into the formula bar, and then press the Enter key. 2. Then select cell C2, and drag the Fill Handle down to cells you need to concatenate.
try this -
SELECT DISTINCT
fileid
, STUFF((
SELECT N', ' + CAST([filename] AS VARCHAR(255))
FROM tblFile f2
WHERE f1.fileid = f2.fileid ---- string with grouping by fileid
FOR XML PATH (''), TYPE), 1, 2, '') AS FileNameString
FROM tblFile f1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With