Split Record into two resulting rows

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 2008 Forums

Transact-SQL (2008)

Split Record into two resulting rows

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2013-09-29 : 10:40:18

Hi. I need some help with formulating a SQL Statement.

I have a table that logs down transfer of inventory record.
In 1 row of record, it stores the item code, from location, to location and the quantity transferred.

I want to formulate a statement that returns 2 records for each transfer record.

I know it sounds confusing. Perhaps the example below will clarify what I'm trying to achieve.

dbo.Transfer table


TxId	Item	Name	FromLocationId	ToLocationId	Qty
1	9001	Pen	10		11		100

Result I've trying to achieve
Note: I need the qty to indicate +ve and -ve as indicated below


TxId	Item	Name	FromLocation	Qty
1	9001	Pen	10		-100
1	9001	Pen	11		+100

Any suggestions?

Thanks very much in advance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-29 : 17:09:45

[code]select
TxId,
Item,
Name,
FromLocationId as FromLocation,
-Qty as Qty
from
YourTable
union all
select
TxId,
Item,
Name,
ToLocationId as FromLocation,
Qty as Qty
from
YourTable;[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-30 : 06:31:13

[code]
select
t.TxId,
t.Item,
t.Name,
CASE WHEN mult=-1 THEN FromLocationId ELSE ToLocationId END as FromLocation,
Qty * mult as Qty
from
YourTable t
cross join (select -1 as mult union all select 1)m
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

hayashiryo
Starting Member

46 Posts

Posted - 2013-10-02 : 22:38:40

Thanks all. Works nicely now

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 01:31:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources