divide a dinamically table - 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 2008 Forums

Transact-SQL (2008)

divide a dinamically table

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2013-01-11 : 14:28:00

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2013-01-11 : 14:38:35

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

sebastian11c
Posting Yak Master

129 Posts

Posted - 2013-01-11 : 17:41:23

i have got an answer

using the command ntile

http://msdn.microsoft.com/en-us/library/ms175126.aspx

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2013-01-12 : 02:22:18

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"

sebastian11c
Posting Yak Master

129 Posts

Posted - 2013-01-12 : 10:16:56

this is the code .. it works for me

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

regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-13 : 23:00:33

you could also use TOP 50 PERCENT for achieving the same thing

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources