converting rows into columns in t-sql - sql server

问题:

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?

回答1:

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

回答2:

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

回答3:

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.

回答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