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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Carry Over Calc in a Dynamic Table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kalaiselvan
Posting Yak Master

India
112 Posts

Posted - 02/05/2013 :  03:54:57  Show Profile  Reply with Quote
Hi,
Below were the 2 tables. Both were Dynamic. We cant determine the number of Columns in both tables. Columns might increased.
From the 2nd Table data I need a calculated Table.

TABLE A:

[ID] [COL1] [COL2] [COL3]
1 100 110 150
2 90 100 120
3 75 85 110

TABLE B:

[ID] [COLUMNS] [CO] [EQ]
1 COL1 0.2 100
2 COL2 0.5 110
3 COL3 0 90

From Table B Column [CO] is used for calculation. Output table will be

[ID] [COL1] [COL2] [COL3]
1 100 110 150
2 90+(100*0.2) 100+(110*0.5) 120
3 75+((90+(100*0.2))*0.2) 85+((100+(110*0.5))*0.5) 110

If [CO] applied for a column then the Calculation starts. 1st Row will be as it is. And 2nd row will be added to the 1st Row*[CO]. Like the same for all rows. If [CO] is 0 then no calc.

Output Will be;
[ID] [COL1] [COL2] [COL3]
1 100 110 150
2 110 155 120
3 97 162.5 110

Please help me to fix this with a Query. Note that the Columns will be dynamic.

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/05/2013 :  04:02:03  Show Profile  Reply with Quote
see below. this should give you idea to get started

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx



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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/05/2013 :  04:15:38  Show Profile  Reply with Quote
here's the full illustration for your example


declare @tableA table
(
[ID] int,
[COL1] int,
[COL2] int,
[COL3] int
)
insert @tableA
values(1, 100, 110, 150),
(2, 90, 100, 120),
(3, 75, 85, 110)

declare @tableB table
(
[ID] int,
[COLUMNS] varchar(10),
 [CO] decimal(5,1),
 [EQ] int
)

insert @tableB
values(1, 'COL1', 0.2, 100),
(2, 'COL2', 0.5, 110),
(3, 'COL3', 0, 90)

;With CTE1
AS
(
SELECT *
FROM @tableA t
UNPIVOT( [Val] FOR [COLUMNS] IN ([COL1],[COL2],[COL3]))u
),
CTE2
AS
(
SELECT ID,CAST(Val AS float) AS Val,COLUMNS
FROM CTE1 c1
WHERE NOT EXISTS(SELECT 1 FROM CTE1 WHERE ID < c1.ID)
UNION ALL
SELECT c1.ID,CAST(c1.Val + (c2.Val * b.CO) AS float),c1.COLUMNS
FROM CTE2 c2
INNER JOIN CTE1 c1
ON c1.ID = c2.ID + 1 
AND c1.COLUMNS=c2.COLUMNS
INNER JOIN @tableB b
ON b.COLUMNS=c2.COLUMNS
)

SELECT *
FROM CTE2
PIVOT(SUM(Val) FOR COLUMNS IN ([COL1],[COL2],[COL3]))p

output
----------------------------------------------
ID	COL1	COL2	COL3
1	100	110	150
2	110	155	120
3	97	162.5	110



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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000