ANOTHER WAY INSTEAD OF CURSOR? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

ANOTHER WAY INSTEAD OF CURSOR?

Author

Topic

johnnyrocket5
Starting Member

4 Posts

Posted - 2008-03-19 : 15:12:40

Hello everyone, I have the following situation, I know I could do this with cursors, but I would like to know if perhaps there's a way to do this without the use of them...(SQL code at the bottom of post)

I have created a look up table with two columns: Type, Member - so, the data will look like this when queried:

TYPE..............MEMBER
Preparation......._SKU_MFG_Grind
Preparation......._SKU_MFG_Press
Scrap Sales......._SKU_MFG_ScrapSales
Support..........._SKU_MFG_AP
Support..........._SKU_MFG_PPDIT
Support _SKU_MFG_Purchasing_RawMaterials

The look up table gives me a category and it's members which I need to get from another table - The members represent columns from the other table. These columns need to be summed into a new column according to category - so you'd end up with something like this:

<Column1>Support = [_SKU_MFG_AP] + [SKU_MFG] + [PPDIT]+ [_SKU_MFG_Purchasing_RawMaterials]

<Column2>Preparation = [_SKU_MFG_Grind] + [_SKU_MFG_Press]

..and so on...

So my question is...I need to build the dynamic sql that select the appropriate columns according to the categories and it's members and sum them up into columns. I know I could do this with a cursor that gets the distinct members, then another to loop through the members and generate the sql, but is there another way?

Here's the code to create the tables:

--CREATE LOOKUP TABLE
if OBJECT_ID('tempdb..PSManufLookup', 'U') IS NOT NULL
DROP TABLE PSManufLookup

CREATE TABLE [dbo].[PSManufLookup](
[Type] [varchar](100) NULL,
[Member] [varchar](100) NULL
) ON [PRIMARY]

--INSERT TYPES AND MEMBERS

INSERT INTO PSMANUFLOOKUP (TYPE, MEMBER)
SELECT 'Material', '_SKU_MFG_Cogs'
union
SELECT 'Scrap Sales', '_SKU_MFG_ScrapSales'
UNION
SELECT 'Inventory Var', '_SKU_MFG_InverntoryAdjustments'
UNION
SELECT 'Preparation','_SKU_MFG_Grind'
UNION
SELECT 'Preparation','_SKU_MFG_Press'
UNION
SELECT 'Support','_SKU_MFG_AP'
UNION
SELECT 'Support','_SKU_MFG_Purchasing_RawMaterials'
UNION
SELECT 'Support','_SKU_MFG_SupplyChain_DomesticRawMaterials'
UNION
SELECT 'Support','_SKU_MFG_SupplyChain_ImportRawMaterials'
UNION
SELECT 'Support','_SKU_MFG_PPDIT'
UNION
SELECT 'Volume Related Costs','_SKU_MFG_MatHandling'
UNION
SELECT 'Volume Related Costs','_SKU_MFG_MROCost'
UNION
SELECT 'Volume Related Costs','_SKU_MFG_Tooling'
UNION
SELECT 'Volume Related Costs','_SKU_MFG_Wash'
UNION
SELECT 'Mfg Carrying Cost','_SKU_MFG_CarryingCost'
UNION
SELECT 'Mfg Freight','_SKU_MFG_InboundFreight'
UNION
SELECT 'Mfg Freight','_SKU_MFG_InterplantFreight'
UNION
SELECT 'Mfg Finishing','_SKU_MFG_Finishing'

--table where data resides

CREATE TABLE [dbo].[sourcedata](
[_SKU_NewProductFlag] [varchar](1) NULL,
[_SKU_NoDaysActiveProduct] [int] NULL,
[_SKU_NoUnitsSold] [int] NULL,
[_SKU_MFG_CarryingCost] [float] NULL,
[_SKU_MFG_Tooling] [float] NULL,
[_SKU_MFG_Wash] [float] NULL,
[_SKU_MFG_Grind] [float] NULL,
[_SKU_MFG_Press] [float] NULL,
[_SKU_MFG_MROCost] [float] NULL,
[_SKU_MFG_MatlHandling] [float] NULL,
[_SKU_MFG_Storage] [float] NULL,
[_SKU_MFG_ScrapSales] [float] NULL,
[_SKU_MFG_InventoryAdjustments] [float] NULL,
[_SKU_MFG_COGS] [float] NULL,
[_SKU_MFG_SupplyChain_ImportRawMaterials] [float] NULL,
[_SKU_MFG_SupplyChain_DomesticRawMaterials] [float] NULL,
[_SKU_MFG_Purchasing_RawMaterials] [float] NULL,
[_SKU_MFG_PPDIT] [float] NULL,
[_SKU_MFG_AP] [float] NULL,
[_SKU_MFG_InterplantFreight] [float] NULL,
[_SKU_MFG_InboundFreight] [float] NULL,
[_SKU_MFG_Finishing] [float] NULL )

tm
Posting Yak Master

160 Posts

Posted - 2008-03-19 : 16:08:54

Check out ..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources