converting rows into columns in t-sql - sql server 2005

Question

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?

Answer 1

SQL 2005 has a PIVOT function that should do what you want. http://msdn.microsoft.com/en-us/library/ms177410.aspx

Answer 2

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.

Answer 3

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

Answer 4

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