summing horiz. not vert. - 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

General SQL Server Forums

New to SQL Server Programming

summing horiz. not vert.

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-10 : 09:31:13

As of right now, I am migrating from Access to SQL Express. I have a lot of things running smooth expect for the implementation of a couple items. I created some udf's in Access which I would call up to 5 times in a single query. This allowed me to sum across columns kind of like this:
table1
col1,col2,col3
xxx,all,20
xxx,1,10
yyy,5,100

query1
col1,col2,col3,col4,col5,col6
xxx,30,20,20,20,20
yyy,null,null,null,null,100

I can post the code from the access udf (i passed in variables from col1,col2 in table1 and did comparisons). I'm not sure how to write functions in sql yet, or there might be a built-in way unlike access to do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 09:37:17

If you use version 2005, you can use PIVOT operator

Madhivanan

Failing to plan is Planning to fail

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-10 : 09:48:27

I'm using express 2008 and the pivot idea looks good, only problem is with the category of all that needs to summed into all columns (the examples I see with pivot work well with putting the summed values into col1,col2,col3 resp. but making sure if all is the category to sum that into all the columns.

EDIT - Tried to follow and example on pivots, but so far everything came up NULL.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-10 : 10:16:56

quote:
Originally posted by detlion1643

I'm using express 2008 and the pivot idea looks good, only problem is with the category of all that needs to summed into all columns (the examples I see with pivot work well with putting the summed values into col1,col2,col3 resp. but making sure if all is the category to sum that into all the columns.

EDIT - Tried to follow and example on pivots, but so far everything came up NULL.

For Better understanding .i kindly request you to look for Mr.Madhi blog
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-10 : 10:33:12

Alright, got it working for the most part... just one hiccup.

SELECT [Part #],IM,TD,SY,WY,DH,CP
FROM (
SELECT Flat.[Part #],Flat.[From],Flat.[$]
FROM Flat) up
PIVOT
( SUM([$]) FOR [FROM] IN (IM,TD,SY,WY,DH,CP)) AS pvt
ORDER BY [Part #]
GO

If my from column (I know bad name for column) contains 'MFG' then I need to sum it to all the columns (IM,TD,SY,WY,DH,CP). Is it possible to modify the code to check for 'MFG' and if it is sum to all?

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-10 : 10:37:01

quote:
Originally posted by detlion1643

Alright, got it working for the most part... just one hiccup.
SELECT [Part #],IM,TD,SY,WY,DH,CP
FROM (
SELECT Flat.[Part #],Flat.[From],Flat.[$]
FROM Flat) up
PIVOT
( SUM([$]) FOR [FROM] IN (IM,TD,SY,WY,DH,CP)) AS pvt
ORDER BY [Part #]
GO

If my from column (I know bad name for column) contains 'MFG' then I need to sum it to all the columns (IM,TD,SY,WY,DH,CP). Is it possible to modify the code to check for 'MFG' and if it is sum to all?

HAVE you gone through the link i provided above..hope you can do all with pivot..
sorry,iam not at the workstation now..please use for Case when Approach like
sum(case when col=value then col else 0 end)

HTH

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-10 : 10:46:50

Yes, I have gone through the link provided (thanks). I've been trying to use the CASE statement in various places, but everything comes up with syntax errors. Oh well, guess I'll keep working at it.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-10 : 10:47:37

quote:
Originally posted by detlion1643

Yes, I have gone through the link provided (thanks). I've been trying to use the CASE statement in various places, but everything comes up with syntax errors. Oh well, guess I'll keep working at it.

Could you please post over..let me try too

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:07:41

quote:
Originally posted by detlion1643

Alright, got it working for the most part... just one hiccup.
SELECT [Part #],IM,TD,SY,WY,DH,CP
FROM (
SELECT Flat.[Part #],Flat.[From],Flat.[$]
FROM Flat) up
PIVOT
( SUM([$]) FOR [FROM] IN (IM,TD,SY,WY,DH,CP)) AS pvt
ORDER BY [Part #]
GO

If my from column (I know bad name for column) contains 'MFG' then I need to sum it to all the columns (IM,TD,SY,WY,DH,CP). Is it possible to modify the code to check for 'MFG' and if it is sum to all?

do you mean this?


SELECT [Part #],IM+MFG AS IM,
TD + MFG AS TD,
SY + MFG AS SY,
WY + MFG AS WY,
DH + MFG AS DH,
CP + MFG AS CP
FROM (
SELECT Flat.[Part #],Flat.[From],Flat.[$]
FROM Flat) up
PIVOT
( SUM([$]) FOR [FROM] IN (IM,TD,SY,WY,DH,CP,MFG)) AS pvt
ORDER BY [Part #]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-10 : 12:45:28

or this
SELECT [Part #],IMAS IM,
TD AS TD,
SY AS SY,
WY AS WY,
DH AS DH,
CP AS CP, (CASE WHEN [FROM]='MFG' Then IM+TD+SY+WY+DH+CP else 0 end) AS MFG
FROM (
SELECT Flat.[Part #],Flat.[From],Flat.[$]
FROM Flat) up
PIVOT
( SUM([$]) FOR [FROM] IN (IM,TD,SY,WY,DH,CP,MFG)) AS pvt
ORDER BY [Part #]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources