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)
 divide a dinamically table
 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
52326 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  
 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