Transforming table rows to columns

Question

Transforming table rows to columns

bdba

SSCommitted

Points: 1504
More actions
June 7, 2006 at 7:16 pm

#90220

Hi I need to write a script to be able to Transform rows to columns from a given table. Below is my sample input and desired output.
Input table:
ID Costs
123 1000.00
123 2000.00
123 3000.00
456 500.00
456 1500.00
789 1200.00
789 5200.00
789 2400.00
Output table/view
ID Cost1 Cost2 Cost3
123 1000.00 2000.00 3000.00
456 500.00 1500.00 0.00
789 1200.00 5200.00 2400.00
Note: for ID 463, the cost3 is 0.00 becuase there is no value in the input table.
We will probably need to use a cursor for this. Wanted to know if anybody has any idea on transforming the data like this.
Any help would be greatly appreciated.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Is there any other data attribute that differentiates the rows?

ID	Costs
123	1000.00
123	2000.00
123	3000.00

How do you know 1000 is cost 1? and not cost 2?

Is it cost 1 merely cuz its first? You cannot count on the order being right

Is there another data attribute that differentiates these values?

for starters look up the case statement,

or google for Pivot Table.

Jason- SSCrazy Points: 2795 More actions · Answer 2

Do a search on this site for Pivot Table. Each listed applies to your question but are written to accept input that include three columns of data not 2.

Browse through the scripts listed and play with them to see if you can get them to work: I found this one to be good, but I couldn't get it to work with your input

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=422

Wish I could help more, but it's late.

Jason

-

bdba SSCommitted Points: 1504 More actions · Answer 3

Thanks for your feedback guys.

However, i was wondering if anybody has done this using cursors.?

thanks

Jason- SSCrazy Points: 2795 More actions · Answer 4

This can be done using a cursor, but cursors are generally avoided because of performance, resources, etc. Below is a script that will work with your data sets. Instead of cursors I used local variable tables - although if you have to use a cursor the logic is easily replaced.

You'll need to modify it to your needs for table names, columns, datatypes and general clean up.

On a side note, there are probably more efficient ways to do this (one being a cube) but this should work for you.

I created a table called tblPivotData and inserted your data into it. Executed the below script and the output returns:

iCostId rCost1 rCost2 rCost3

----------- ------------------------ ------------------------ ------------------------

123 1000.0 2000.0 3000.0

456 500.0 1500.0 0.0

789 1200.0 5200.0 2400.0

To see the output of the script w/o modifications execute the below to create and populate the tblPivotdata table (Note that there two cost id's not in your data set... This for illustration of what the script will do with the CostId vs Cost):

Create Table tblPivotData (iCostId INT,rCost REAL)

INSERT INTO tblPivotData Select 123,1000

INSERT INTO tblPivotData Select 123,2000

INSERT INTO tblPivotData Select 123,3000

INSERT INTO tblPivotData Select 456,500

INSERT INTO tblPivotData Select 456,1500

INSERT INTO tblPivotData Select 789,1200

INSERT INTO tblPivotData Select 789,5200

INSERT INTO tblPivotData Select 789,2400

INSERT INTO tblPivotData Select 111,1200

INSERT INTO tblPivotData Select 111,5200

INSERT INTO tblPivotData Select 555,2400

INSERT INTO tblPivotData Select 555,2900

--------------------------------------------------------------

RUN THIS SCRIPT AFTER THE TABLE IS CREATED

--------------------------------------------------------------

DECLARE @iCostCount INT

DECLARE @iMaxColumns INT

DECLARE @iColumn INT

DECLARE @iRowId INT

DECLARE @iRowId2 INT

DECLARE @iIdCount INT

DECLARE @iCostId INT

DECLARE @rCost REAL

DECLARE @vcExecSQL VARCHAR(8000)

DECLARE @vcInsertString VARCHAR(8000)

DECLARE @tblCosts TABLE (iRowId INT IDENTITY(1,1),iCostId INT, iCostCount INT)

DECLARE @tblInsertData TABLE (iRowId INT IDENTITY(1,1),rCost REAL)

DECLARE @iFirst INT

INSERT INTO @tblCosts Select distinct iCostId,count(*) from tblPivotData group by iCostId Order by iCostId DESC

SET @iRowId = @@ROWCOUNT

SET @iMaxColumns = (Select max(iCostCount) from @tblCosts)

SET @iColumn = 1

SET @vcExecSQL = 'IF EXISTS(Select * from sysobjects where id = OBJECT_ID(N''tblPivotedData''))'

+' DROP TABLE tblPivotedData'

EXEC (@vcExecSQL)

SET @vcExecSQL = 'CREATE TABLE tblPivotedData(iCostId INT NOT NULL,'

WHILE @iColumn <= @iMaxColumns

BEGIN

IF @iColumn = @iMaxColumns

SET @vcExecSQL = @vcExecSQL+'rCost'+cast(@iColumn as varchar)+' REAL CONSTRAINT DF_rCost_'+cast(@iColumn as varchar)+' DEFAULT (0))'

ELSE

SET @vcExecSQL = @vcExecSQL+'rCost'+cast(@iColumn as varchar)+' REAL CONSTRAINT DF_rCost_'+cast(@iColumn as varchar)+' DEFAULT (0),'

SET @iColumn = @iColumn + 1

END

EXEC (@vcExecSQL)

DECLARE @iColCount INT

DECLARE @i INT

WHILE @iRowId > 0

BEGIN

SET @iCostId = (Select iCostId from @tblCosts where iRowId = @iRowId)

INSERT INTO @tblInsertData Select rCost from tblPivotData where iCostId = @iCostId

SET @iColCount = @@ROWCOUNT

SET @iRowId2 = (Select max(iRowId) from @tblInsertData)

SET @iFirst = 1

SET @vcInsertString = 'INSERT INTO tblPivotedData (iCostId'

WHILE @iColCount > 0

BEGIN

SET @vcInsertString = @vcInsertString+',rCost'+cast(@iColCount as varchar)

SET @iColCount = @iColCount-1

END

SET @vcInsertString = @vcInsertString+') SELECT '+cast(@iCostId as varchar)+','

WHILE @iRowId2 >= (select min(iRowId) from @tblInsertData)

BEGIN

SET @rCost = (Select rCost from @tblInsertData where iRowId = @iRowId2)

IF @iFirst = 1

BEGIN

SET @vcInsertString = @vcInsertString+' '+cast(@rCost as varchar)

SET @iFirst = 0

END

ELSE

SET @vcInsertString = @vcInsertString+','+cast(@rCost as varchar)

SET @iRowId2 = @iRowId2-1

END

EXEC (@vcInsertString)

delete from @tblInsertData

SET @iRowId = @iRowId-1

END

Select * from tblPivotedData

-

bdba SSCommitted Points: 1504 More actions · Answer 5

Thanks a lot Jason. This works. I cant thank you enough for this.

I was trying this to see if it will work if my second column in the input table is a varchar datatype rather than integer. Eg instead of cost if it is instrument and i want to transform it into instrument1, instrument2, instrument3. It ofcourse fails becuase the insert statement throws an error as it is doing select on varchar values withough close quoations. Would you know a way to make it work.

I have modified the script like pasted below.

Create Table tblPivotData (iCostID INT,instrument varchar(50))

INSERT INTO tblPivotData Select 123,'abc'

INSERT INTO tblPivotData Select 123,'dvb'

INSERT INTO tblPivotData Select 123,'dgd'

INSERT INTO tblPivotData Select 456,'des'

INSERT INTO tblPivotData Select 456,'dfd'

INSERT INTO tblPivotData Select 789,'dge'

INSERT INTO tblPivotData Select 789,'dgd'

INSERT INTO tblPivotData Select 789,'dfd'

INSERT INTO tblPivotData Select 111,'dere'

INSERT INTO tblPivotData Select 111,'dfd'

INSERT INTO tblPivotData Select 555,'dfd'

INSERT INTO tblPivotData Select 555,'adf'

--------------------------------------------------------------

RUN THIS SCRIPT AFTER THE TABLE IS CREATED

--------------------------------------------------------------

DECLARE @iCostCount INT

DECLARE @iMaxColumns INT

DECLARE @iColumn INT

DECLARE @iRowId INT

DECLARE @iRowId2 INT

DECLARE @iIdCount INT

DECLARE @iCostID INT

DECLARE @instrument varchar

DECLARE @vcExecSQL VARCHAR(8000)

DECLARE @vcInsertString VARCHAR(8000)

DECLARE @tblCosts TABLE (iRowId INT IDENTITY(1,1),iCostID INT, iCostCount INT)

DECLARE @tblInsertData TABLE (iRowId INT IDENTITY(1,1),instrument varchar(50))

DECLARE @iFirst INT

INSERT INTO @tblCosts Select distinct iCostID,count(*) from tblPivotData group by iCostID Order by iCostID DESC

SET @iRowId = @@ROWCOUNT

SET @iMaxColumns = (Select max(iCostCount) from @tblCosts)

SET @iColumn = 1

SET @vcExecSQL = 'IF EXISTS(Select * from sysobjects where id = OBJECT_ID(N''tblPivotedData''))'

+' DROP TABLE tblPivotedData'

EXEC (@vcExecSQL)

SET @vcExecSQL = 'CREATE TABLE tblPivotedData(iCostID INT NOT NULL,'

WHILE @iColumn <= @iMaxColumns

BEGIN

IF @iColumn = @iMaxColumns

SET @vcExecSQL = @vcExecSQL+'instrument'+cast(@iColumn as varchar)+' Varchar(50) CONSTRAINT DF_instrument_'+cast(@iColumn as varchar)+' DEFAULT (0))'

ELSE

SET @vcExecSQL = @vcExecSQL+'instrument'+cast(@iColumn as varchar)+' Varchar(50) CONSTRAINT DF_instrument_'+cast(@iColumn as varchar)+' DEFAULT (0),'

SET @iColumn = @iColumn + 1

END

EXEC (@vcExecSQL)

DECLARE @iColCount INT

DECLARE @i INT

WHILE @iRowId > 0

BEGIN

SET @iCostID = (Select iCostID from @tblCosts where iRowId = @iRowId)

INSERT INTO @tblInsertData Select instrument from tblPivotData where iCostID = @iCostID

SET @iColCount = @@ROWCOUNT

SET @iRowId2 = (Select max(iRowId) from @tblInsertData)

SET @iFirst = 1

SET @vcInsertString = 'INSERT INTO tblPivotedData (iCostID'

WHILE @iColCount > 0

BEGIN

SET @vcInsertString = @vcInsertString+',instrument'+cast(@iColCount as varchar)

SET @iColCount = @iColCount-1

END

SET @vcInsertString = @vcInsertString+') SELECT '+cast(@iCostID as varchar)+','

WHILE @iRowId2 >= (select min(iRowId) from @tblInsertData)

BEGIN

SET @instrument = (Select instrument from @tblInsertData where iRowId = @iRowId2)

IF @iFirst = 1

BEGIN

SET @vcInsertString = @vcInsertString+' '+cast(@instrument as varchar(50))

SET @iFirst = 0

END

ELSE

SET @vcInsertString = @vcInsertString+','+cast(@instrument as varchar(50))

SET @iRowId2 = @iRowId2-1

END

EXEC (@vcInsertString)

print @vcInsertString

delete from @tblInsertData

SET @iRowId = @iRowId-1

END

Select * from tblPivotedData

Jason- SSCrazy Points: 2795 More actions · Answer 6

I took out the cast function just to make it neater since you don't have to covert a varchar to a varchar, then added single quotes so that the print @vcExecSQL will now look like this:

INSERT INTO tblPivotedData (iCostID,instrument3,instrument2,instrument1) SELECT 123, 'd','d','a'

Here is the script with the modifications, I hope this will work for you, let me know.

if exists(select * from sysobjects where id = OBJECT_ID(N'tblPivotData'))

DROP TABLE tblPivotData

GO

Create Table tblPivotData (iCostID INT,instrument varchar(50))

INSERT INTO tblPivotData Select 123,'abc'

INSERT INTO tblPivotData Select 123,'dvb'

INSERT INTO tblPivotData Select 123,'dgd'

INSERT INTO tblPivotData Select 456,'des'

INSERT INTO tblPivotData Select 456,'dfd'

INSERT INTO tblPivotData Select 789,'dge'

INSERT INTO tblPivotData Select 789,'dgd'

INSERT INTO tblPivotData Select 789,'dfd'

INSERT INTO tblPivotData Select 111,'dere'

INSERT INTO tblPivotData Select 111,'dfd'

INSERT INTO tblPivotData Select 555,'dfd'

INSERT INTO tblPivotData Select 555,'adf'

--------------------------------------------------------------

-- RUN THIS SCRIPT AFTER THE TABLE IS CREATED

--------------------------------------------------------------

DECLARE @iCostCount INT

DECLARE @iMaxColumns INT

DECLARE @iColumn INT

DECLARE @iRowId INT

DECLARE @iRowId2 INT

DECLARE @iIdCount INT

DECLARE @iCostID INT

DECLARE @instrument varchar

DECLARE @vcExecSQL VARCHAR(8000)

DECLARE @vcInsertString VARCHAR(8000)

DECLARE @tblCosts TABLE (iRowId INT IDENTITY(1,1),iCostID INT, iCostCount INT)

DECLARE @tblInsertData TABLE (iRowId INT IDENTITY(1,1),instrument varchar(50))

DECLARE @iFirst INT

INSERT INTO @tblCosts Select distinct iCostID,count(*) from tblPivotData group by iCostID Order by iCostID DESC

SET @iRowId = @@ROWCOUNT

SET @iMaxColumns = (Select max(iCostCount) from @tblCosts)

SET @iColumn = 1

SET @vcExecSQL = 'IF EXISTS(Select * from sysobjects where id = OBJECT_ID(N''tblPivotedData''))'

+' DROP TABLE tblPivotedData'

EXEC (@vcExecSQL)

SET @vcExecSQL = 'CREATE TABLE tblPivotedData(iCostID INT NOT NULL,'

WHILE @iColumn <= @iMaxColumns

BEGIN

IF @iColumn = @iMaxColumns

SET @vcExecSQL = @vcExecSQL+'instrument'+cast(@iColumn as varchar)+' Varchar(50) CONSTRAINT DF_instrument_'+cast(@iColumn as varchar)+' DEFAULT (0))'

ELSE

SET @vcExecSQL = @vcExecSQL+'instrument'+cast(@iColumn as varchar)+' Varchar(50) CONSTRAINT DF_instrument_'+cast(@iColumn as varchar)+' DEFAULT (0),'

SET @iColumn = @iColumn + 1

END

EXEC (@vcExecSQL)

DECLARE @iColCount INT

DECLARE @i INT

WHILE @iRowId > 0

BEGIN

SET @iCostID = (Select iCostID from @tblCosts where iRowId = @iRowId)

INSERT INTO @tblInsertData Select instrument from tblPivotData where iCostID = @iCostID

SET @iColCount = @@ROWCOUNT

SET @iRowId2 = (Select max(iRowId) from @tblInsertData)

SET @iFirst = 1

SET @vcInsertString = 'INSERT INTO tblPivotedData (iCostID'

WHILE @iColCount > 0

BEGIN

SET @vcInsertString = @vcInsertString+',instrument'+cast(@iColCount as varchar)

SET @iColCount = @iColCount-1

END

SET @vcInsertString = @vcInsertString+') SELECT '+cast(@iCostID as varchar)+','

WHILE @iRowId2 >= (select min(iRowId) from @tblInsertData)

BEGIN

SET @instrument = (Select instrument from @tblInsertData where iRowId = @iRowId2)

IF @iFirst = 1

BEGIN

SET @vcInsertString = @vcInsertString+' '''+@instrument+''''

SET @iFirst = 0

END

ELSE

SET @vcInsertString = @vcInsertString+','''+@instrument+''''

SET @iRowId2 = @iRowId2-1

END

EXEC (@vcInsertString)

print @vcInsertString

delete from @tblInsertData

SET @iRowId = @iRowId-1

END

Select * from tblPivotedData

-

bdba SSCommitted Points: 1504 More actions · Answer 7

Thanks a bunch Jason for providing the solution to handle the varchar datatype. I really appreciate your assistance on this.

cheers,

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 8

Are you using SQL Server 2005? One query does the trick:

-- Set up sample dataDECLARE @Source table (CostId int NOT NULL, Cost decimal(6, 2) NOT NULL)  INSERT @Source(CostID, Cost)  SELECT 123,1000 UNION SELECT 123,2000 UNION SELECT 123,3000 UNION SELECT 456,500
  UNION SELECT 456,1500 UNION SELECT 789,1200 UNION SELECT 789,5200 UNION SELECT 789,2400
  UNION SELECT 111,1200 UNION SELECT 111,5200 UNION SELECT 555,2400 UNION SELECT 555,2900
 
-- Just for reference: this query is the derived table used as the input to the PIVOT in the solution below
-- It may be commented outSELECT CostID, Cost,        'Cost' + CONVERT(varchar(3), DENSE_RANK() OVER (PARTITION BY CostID ORDER BY CostID, Cost)) AS CostRank  FROM @Source 
-- The entire solution in one query: pivot the CostX columns to produce the resultsSELECT CostID, IsNull(Cost1, 0) AS [Cost1], IsNull(Cost2, 0) AS [Cost2],       IsNull(Cost3, 0) AS [Cost3], IsNull(Cost4, 0) AS [Cost4]  FROM (        SELECT CostID, Cost,                'Cost' + CONVERT(varchar(3), DENSE_RANK() OVER (PARTITION BY CostID ORDER BY CostID, Cost)) AS CostRank          FROM @Source       ) a PIVOT       (SUM(Cost) FOR CostRank IN (Cost1, Cost2, Cost3, Cost4)) pvt ORDER BY CostID 
 
The results:
--This is the input table to the PIVOTCostID  Cost    CostRank
   111 1200.00  Cost1
   111 5200.00  Cost2
   123 1000.00  Cost1
   123 2000.00  Cost2
   123 3000.00  Cost3
   456  500.00  Cost1
   456 1500.00  Cost2
   555 2400.00  Cost1
   555 2900.00  Cost2
   789 1200.00  Cost1
   789 2400.00  Cost2
   789 5200.00  Cost3
 -- This is the result of the PIVOTCostID Cost1   Cost2   Cost3    Cost4   111 1200.00 5200.00    0.00  0.00
   123 1000.00 2000.00 3000.00  0.00
   456  500.00 1500.00    0.00  0.00
   555 2400.00 2900.00    0.00  0.00
   789 1200.00 2400.00 5200.00  0.00

-Eddie

Eddie Wuerch
MCM: SQL

ID	Cost1	Cost2	Cost3
123	1000.00	2000.00	3000.00
456	500.00	1500.00	0.00
789	1200.00	5200.00	2400.00