Tutorial :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?


Solution: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


Solution:2

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


Solution: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.


Solution: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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »