Software Code Help - Concatenate Field Values in One String using Group By in SQL Server 2005

Tuesday, September 29, 2009

Concatenate Field Values in One String using Group By in SQL Server 2005 by Raj Shekhar

For Example:

Number Value.....
1          AD
2          GD
2          DE
3          IF
4          OG
4          JD

The output I would like is:-

1         AD
2         GD,DE
3         IF
4         OG,JD

You can also resolve this problem using function.

Create Function Concat( @iNumber int )
RETURNS varchar(500)
AS
BEGIN
   DECLARE @List varchar(500)
   SET @List = ''
   Select @List = @List + strValue + ',' from Testing
   Where Number = @iNumber
   RETURN LEFT(@List,LEN(@List)-1)
End

And use this function in SQL Query.

select Number, dbo.Concat(Number) from Testing Group By Number

Tuesday, September 29, 2009 3:46:35 AM (GMT Daylight Time, UTC+01:00)

Comments [1] SQL Server |

Wednesday, September 30, 2009 3:53:09 AM (GMT Daylight Time, UTC+01:00)

Select Distinct Number,
(SELECT LEFT(TempTable.strValue,LEN(TempTable.strValue)-1) As List
FROM (
SELECT cast(strValue as varchar) + ',' as strValue
FROM Testing WITH (NOLOCK) Where Number = T.Number
FOR XML PATH('')
)
TempTable(strValue))
From Testing T

Pawan Khatri

Comments are closed.