SQL Server Forums
Profile | Register | 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)
 Split Record into two resulting rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hayashiryo
Starting Member

37 Posts

Posted - 09/29/2013 :  10:40:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 09/29/2013 :  17:09:45  Show Profile  Reply with Quote
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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/30/2013 :  06:31:13  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

hayashiryo
Starting Member

37 Posts

Posted - 10/02/2013 :  22:38:40  Show Profile  Reply with Quote
Thanks all. Works nicely now
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/04/2013 :  01:31:03  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000