Convert rows to columns in t-sql-sql server 2005

Question

I have a situation where 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 the 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 for this?

Answer 1

Assuming your data is in a table:

 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 single id, dbo.toCSV (id) from aTable

Answer 2

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

Answer 3

You can do this with nested selections or more easily with the rotation operator, although you should know all the possible values ​​before you get started.

If you want it to be dynamic, you will need a cursor and some dynamic SQL.

Answer 4

A simple example of the COALESCE function:

One Temp table was created in which I put one 9 rows using the WHILE loop. In the main part, I just applied the Colall to COALESCE function.

TABLE DROP #Material SET NOCOUNT ON CREATE TABLE #Material (

 MaterialID INT 

)

DECLARE @LoopCounter INT DECLARE @MaxLoopCounter INT

SET @LoopCounter = 1 SET @MaxLoopCounter = 10

WHILE (@LoopCounter <@MaxLoopCounter) START 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

PRINTING 'EXIT EXIT:' + @MaterialID

- SELECT * FROM #Material SET NOCOUNT OFF