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)
 divide a dinamically table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sebastian11c
Posting Yak Master

129 Posts

Posted - 01/11/2013 :  14:28:00  Show Profile  Reply with Quote
hi there ,
one more time i need your help

i have a dinamically table (it could have 10 or 13 or 17 or 20 rows or ..)

but im goint to use a table with 5 rows

INSERT INTO [dbo].[parts]
([idpart]
,[part_name]
)

select 1, 'door'
union
select 2, 'window'
union
select 3, 'table'
union
select 4, 'chair'
union
select 5, 'pencil'



that i need to do its divide the table in 2 parts
if the number of rows are multiple of 2 , both tables will have the same number of rows , but if not one table have more rows than the other

the result that i need is this

table 1

1, 'door'

2, 'window'

3, 'table'


table 2
4, 'chair'

5, 'pencil'



i need a query to do this dinamically, beacuse the number of rows are variable

many thanks for share your knowledge

regards

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/11/2013 :  14:38:35  Show Profile  Reply with Quote
Would this work for you?
DECLARE @n INT;
SELECT @n = COUNT(*) FROM Tbl;

INSERT INTO TBL1 SELECT TOP ((@n+1)/2) * FROM Tbl ORDER BY idpart ASC
INSERT INTO TBL2 SELECT TOP (@n/2) * FROM Tbl ORDER BY idpart DESC
Go to Top of Page

sebastian11c
Posting Yak Master

129 Posts

Posted - 01/11/2013 :  17:41:23  Show Profile  Reply with Quote
i have got an answer

using the command ntile

http://msdn.microsoft.com/en-us/library/ms175126.aspx
Go to Top of Page

vinu.vijayan
Posting Yak Master

India
227 Posts

Posted - 01/12/2013 :  02:22:18  Show Profile  Reply with Quote
Could you please share the script with us??....It would be of great help for other readers of this post.

N 28° 33' 11.93148"
E 77° 14' 33.66384"
Go to Top of Page

sebastian11c
Posting Yak Master

129 Posts

Posted - 01/12/2013 :  10:16:56  Show Profile  Reply with Quote
this is the code .. it works for me



select idpart, part_name, NTILE(2) over (order by idpart) as group_number from parts

regards
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/13/2013 :  23:00:33  Show Profile  Reply with Quote
you could also use TOP 50 PERCENT for achieving the same thing

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

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.39 seconds. Powered By: Snitz Forums 2000