I have situation that I have a table in the structure
ID, name
1, 'value1'
1, 'value2'
2, 'value3'
2, 'value4'
1, 'value5'
I wanted to show the above data in following format
id , combineName
1 ,'value1','value2', 'value5'
2 ,'value3','value4'
Is there an easy way to do this in SQL Server 2005, or will I have to run cursors to do it?
Assuming your data is in aTable:
create FUNCTION toCSV (@id int)
RETURNS varchar(100)
AS
BEGIN
DECLARE @List varchar(100)
SELECT @List = COALESCE(@List + ', ', '') +
CAST(name AS varchar(10))
FROM aTable
WHERE ID = @id
RETURN(@list)
END;
go
Then:
select distinct id, dbo.toCSV(id) from aTable
SQL 2005 has a PIVOT function that should do what you want. http://msdn.microsoft.com/en-us/library/ms177410.aspx
You can do this using nested selects, or more easily using the pivot operator, although you have to know all of the possible values before hand.
If you want it to be dynamic, then you will need a cursor and some dynamic SQL.
Simple Example of COALESCE Function:
Created one Temp table in which i have put one 9 rows with the help of WHILE loop. The at the Main part i have just take Column to COALESCE function.
DROP TABLE #Material SET NOCOUNT ON CREATE TABLE #Material (
MaterialID INT
)
DECLARE @LoopCounter INT DECLARE @MaxLoopCounter INT
SET @LoopCounter = 1 SET @MaxLoopCounter = 10
WHILE (@LoopCounter < @MaxLoopCounter) BEGIN INSERT INTO #Material (MaterialID) VALUES (@LoopCounter) SET @LoopCounter = @LoopCounter + 1 END
/* MAIN PART */ DECLARE @MaterialID VARCHAR(100)
SELECT @MaterialID = COALESCE(@MaterialID + ',','') + CAST(MaterialID AS VARCHAR(100)) FROM #Material
PRINT 'FINAL OUTPUT: '+ @MaterialID
-- SELECT * FROM #Material SET NOCOUNT OFF