Transforming table rows to columns
-
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
-
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.
-
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://qa.sqlservercentral.com/scripts/viewscript.asp?scriptid=422
Wish I could help more, but it's late.
Jason
-
-
Thanks for your feedback guys.
However, i was wondering if anybody has done this using cursors.?
thanks
-
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
-
-
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
-
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
-
-
Thanks a bunch Jason for providing the solution to handle the varchar datatype. I really appreciate your assistance on this.
cheers,
-
Are you using SQL Server 2005? One query does the trick:
-- Set up sample data
DECLARE
@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 out
SELECT 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 results
SELECT
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)) pvtORDER
BY CostIDThe results:
--This is the input table to the PIVOT
CostID 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 PIVOT
CostID Cost1
Cost2 Cost3 Cost4111 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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply