Need help in Query writing - 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)

Need help in Query writing

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-04 : 01:33:56

i do have one table called invoice with the below structure...

Deal Version Brokerage Broker1 BrokerAmt1 Broker2 BrokerAmt2 Broker3 BrokerAmt3 Broker4 BrokerAmt4 Date
5555 0 2500 2106 625 2111 625 2123 625 2108 625 2006-07-22
5555 1 2500 2103 625 2140 625 2123 625 2108 625 2006-08-22

As you see from data that Broker1 and Broker2 is changed in the month August 2006. Whole Brokerage is divided into four parts [i.e Broker1, Broker2, Broker3, Broker4]

Required Output...

Deal Brokerage Broker1 BrokerAmt1 Broker2 BrokerAmt2 Broker3 BrokerAmt3 Broker4 BrokerAmt4 Broker5 BrokerAmt5 Broker6 BrokerAmt6 Date
5555 2500 2103 625 2140 625 2123 0 2108 625 2106 -625 2111 -625 2006-08-22

It Means now i do have six brokers and i want to split Brokerage such that 2500 = [625+625+625+625-625-625] = 2500

can any one help me how to write query for that?

-- Regards
Prashant Hirani

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-04 : 01:40:06

Your table is not normalized at all ?

KH
[spoiler]Time is always against us[/spoiler]

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-04 : 01:43:02

If possible then help me in query writing...

and how can you say that it's not normalize....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-04 : 02:00:53

use UNPIVOT on your table and then PIVOT it to your required output

refer to BOL on PIVOT & UNPIVOT
http://msdn.microsoft.com/en-us/library/ms177410.aspx

KH
[spoiler]Time is always against us[/spoiler]

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-04 : 02:02:39

Can you please give me any exmaple query which fulfills my requirenment

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-04 : 02:13:40

"and how can you say that it's not normalize...."
A normalized table will looks like this

create table normalized_table
(
Deal int,
Version int,
Broker int,
BrokerAmt decimal(20,2)
)

"Can you please give me any exmaple query which fulfills my requirenment"
Didn't you take a look at the link i posted at all ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources