SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL query

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-09-30 : 08:15:32

Basically, I need to convert rows of data in the table to the columns in a table.

The data in the table is currently in this format

OrderID FiscalYear cost
12 2007 100
12 2008 110
12 2009 120
12 2010 75

and I want to write the SQL to generate the table below.

OrderID FY1 FY2 FY3 FY4
12 100 110 120 75

FY1 -> 2007, FY2 -> 2008, FY3 -> 2009 and so forth.
Is this possible to do in SQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 08:19:40

[code]SELECT OrderID,
SUM(CASE WHEN FiscalYear=2007 THEN Cost ELSE 0 END) AS FY1,
SUM(CASE WHEN FiscalYear=2008 THEN Cost ELSE 0 END) AS FY2,
SUM(CASE WHEN FiscalYear=2009 THEN Cost ELSE 0 END) AS FY3,
SUM(CASE WHEN FiscalYear=2010 THEN Cost ELSE 0 END) AS FY4
FROm YourTable
GROUP BY OrderID[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-30 : 08:25:30

If year values are not fixed
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-09-30 : 08:35:26

Holy cow!!! this is awesome! I didn't expect the responses will be so quick! this is great.

I copied the SQL from visakh16 user into a function (scalar valued function in the database) and I get the following error

"Select statements included within a function cannot return data to a client"

How do i fix this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 08:44:10

[code]CREATE FUNCTION dbo.fnMyData
(
)
RETURNS TABLE
AS
BEGIN
RETURN (
SELECT OrderID,
SUM(CASE WHEN FiscalYear = 2007 THEN Cost ELSE 0 END) AS FY1,
SUM(CASE WHEN FiscalYear = 2008 THEN Cost ELSE 0 END) AS FY2,
SUM(CASE WHEN FiscalYear = 2009 THEN Cost ELSE 0 END) AS FY3,
SUM(CASE WHEN FiscalYear = 2010 THEN Cost ELSE 0 END) AS FY4
FROM YourTable
GROUP BY OrderID
)
END[/code]

E 12°55'05.63"
N 56°04'39.26"

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-09-30 : 09:11:04

Hmmm...I am still having trouble. Below is the whole function. Please help! Thanks a ton!

USE [temp]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnName]
(
-- Add the parameters for the function here
-- 1,2,3,4,5,6... 10 are the fiscal 10 years
@FiscalYear int,
@PurchaseOrderId int,
@ProjectPhaseId int,
@Bucket int
)
RETURNS money
AS
BEGIN
-- Declare the return variable here
DECLARE @Amount money

SET @Amount = 0

IF @Bucket > 0 AND @Bucket <=10
BEGIN
SELECT
SUM(CASE WHEN pp.fiscalyear=@FiscalYear THEN pp.Cost ELSE 0 END) AS FY1,
SUM(CASE WHEN pp.fiscalyear=@FiscalYear+1 THEN pp.Cost ELSE 0 END) AS FY2,
SUM(CASE WHEN pp.fiscalyear=@FiscalYear+2 THEN pp.Cost ELSE 0 END) AS FY3,
SUM(CASE WHEN pp.fiscalyear=@FiscalYear+3 THEN pp.Cost ELSE 0 END) AS FY4
FROM POProject pp
WHERE pp.PurchaseOrderId = @PurchaseOrderId
AND pp.ProjectPhaseId = @ProjectPhaseId
END
ELSE
BEGIN
SELECT @Amount = 0
END
-- Return the result of the function
RETURN @Amount
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:16:49

Are you returning a resultset, or a scalar value?
You can't have both in same function.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:18:12

The function declaration is returning MONEY scalar value.
How will you fit a table (resultset) into that?

E 12°55'05.63"
N 56°04'39.26"

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-09-30 : 09:55:45

OK. I have dropped the scalar function and trying to create a view something like this....

USE [temp]
GO
/****** Object: View [dbo].[view_POProjectPhases] Script Date: 09/30/2008 09:43:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[view_POProjectPhasesRamaa]
AS

SELECT P.PurchaseOrderId, P.ProjectPhaseId,
SUM(CASE WHEN FiscalYear = P.FiscalYear THEN Cost ELSE 0 END) AS FY1,
SUM(CASE WHEN FiscalYear = P.FiscalYear+1 THEN Cost ELSE 0 END) AS FY2,
SUM(CASE WHEN FiscalYear = P.FiscalYear+2 THEN Cost ELSE 0 END) AS FY3,
SUM(CASE WHEN FiscalYear = P.FiscalYear+3 THEN Cost ELSE 0 END) AS FY4,
SUM(CASE WHEN FiscalYear = P.FiscalYear+4 THEN Cost ELSE 0 END) AS FY5,
SUM(CASE WHEN FiscalYear = P.FiscalYear+5 THEN Cost ELSE 0 END) AS FY6,
SUM(CASE WHEN FiscalYear = P.FiscalYear+6 THEN Cost ELSE 0 END) AS FY7,
SUM(CASE WHEN FiscalYear = P.FiscalYear+7 THEN Cost ELSE 0 END) AS FY8,
SUM(CASE WHEN FiscalYear = P.FiscalYear+8 THEN Cost ELSE 0 END) AS FY9,
SUM(CASE WHEN FiscalYear = P.FiscalYear+9 THEN Cost ELSE 0 END) AS FY10
FROM dbo.POProject AS P
Where P.PurchaseOrderId = 12
GROUP BY P.PurchaseOrderId, P.ProjectPhaseId

I don't have any errors in creating the view but the data output is not in the format that I want. The current data output is as follows which is not what I want.

PurchaseOrderId ProjectPhaseID FY1 FY2 FY3 FY4...
12 15 100 0 0 0
12 16 110 0 0 0
12 17 120 0 0 0

I want the data be in the format

PurchaseOrderId ProjectPhaseID FY1 FY2 FY3 FY4...
12 15 100 0 0 0
12 16 0 110 0 0
12 17 0 0 120 0

The data in the actual table (POProject table) is as follows

PurchaseOrderId ProjectPhaseID FiscalYear cost
12 15 2007 100
12 16 2008 110
12 17 2009 120

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 10:03:31

[code]ALTER VIEW [dbo].[view_POProjectPhasesRamaa]
AS

SELECT P.PurchaseOrderId, P.ProjectPhaseId,
SUM(CASE WHEN FiscalYear = 2007 THEN Cost ELSE 0 END) AS FY1,
SUM(CASE WHEN FiscalYear = 2008 THEN Cost ELSE 0 END) AS FY2,
SUM(CASE WHEN FiscalYear = 2009 THEN Cost ELSE 0 END) AS FY3,
SUM(CASE WHEN FiscalYear = 2010 THEN Cost ELSE 0 END) AS FY4,
SUM(CASE WHEN FiscalYear = 2011 THEN Cost ELSE 0 END) AS FY5,
SUM(CASE WHEN FiscalYear = 2012 THEN Cost ELSE 0 END) AS FY6,
SUM(CASE WHEN FiscalYear = 2013 THEN Cost ELSE 0 END) AS FY7,
SUM(CASE WHEN FiscalYear = 2014 THEN Cost ELSE 0 END) AS FY8,
SUM(CASE WHEN FiscalYear = 2015 THEN Cost ELSE 0 END) AS FY9,
SUM(CASE WHEN FiscalYear = 2016 THEN Cost ELSE 0 END) AS FY10
FROM dbo.POProject AS P
Where P.PurchaseOrderId = 12
GROUP BY P.PurchaseOrderId, P.ProjectPhaseId[/code]

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-09-30 : 10:25:36

Is there a way to do create this view without hardcoding the Fiscal years. Because its not always going to start from 2007 or 2008. The first year can be any year but the following 9 years will always be next 9 consecutive years.

Thanks for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 10:30:36

then create a procedure

ALTER PROC [dbo].[POProjectPhasesRamaa]
@StartYear int
AS

SELECT P.PurchaseOrderId, P.ProjectPhaseId,
SUM(CASE WHEN FiscalYear = @StartYear  THEN Cost ELSE 0 END) AS FY1,
SUM(CASE WHEN FiscalYear = @StartYear +1 THEN Cost ELSE 0 END) AS FY2,
SUM(CASE WHEN FiscalYear = @StartYear+2 THEN Cost ELSE 0 END) AS FY3,
SUM(CASE WHEN FiscalYear = @StartYear+3 THEN Cost ELSE 0 END) AS FY4,
SUM(CASE WHEN FiscalYear = @StartYear+4 THEN Cost ELSE 0 END) AS FY5, 
SUM(CASE WHEN FiscalYear = @StartYear+5 THEN Cost ELSE 0 END) AS FY6,
SUM(CASE WHEN FiscalYear = @StartYear+6 THEN Cost ELSE 0 END) AS FY7,
SUM(CASE WHEN FiscalYear = @StartYear+7 THEN Cost ELSE 0 END) AS FY8,
SUM(CASE WHEN FiscalYear = @StartYear+8 THEN Cost ELSE 0 END) AS FY9,
SUM(CASE WHEN FiscalYear = @StartYear+9 THEN Cost ELSE 0 END) AS FY10
FROM dbo.POProject AS P 
Where P.PurchaseOrderId = 12
GROUP BY P.PurchaseOrderId, P.ProjectPhaseId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 10:53:50

Or use a function as described earlier!
Just remember you cannot return both TABLE or MONEY depending on some internal rules.

E 12°55'05.63"
N 56°04'39.26"

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-09-30 : 13:52:53

Cool! thanks much. Appreciate your lightning speed responses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources