How to create select statement and Split Data

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

Other Forums

SQL Server 6.5 \ SQL Server 7.0

How to create select statement and Split Data

Author

Topic

kjubnoi
Starting Member

3 Posts

Posted - 2013-06-26 : 01:23:39

I have output data xx rows from 1 Statement. I want to Split output Data from 1 Statement to 20%, 30%, 50% of xx rows

i have data :
pd_id values
1001 115
1002 65
1003 55
1008 43
1010 33
1011 21
1013 20
1025 12
1033 11
1035 10

i want to show rank and seq follow below :
pd_id values rank seq
1001 115 A 1
1002 65 A 2
1003 55 B 1
1008 43 B 2
1010 33 B 3
1011 21 C 1
1013 20 C 2
1025 12 C 3
1033 11 C 4
1035 10 C 5

pls, help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-26 : 01:52:32

Are you really using SQL Server 6.5 / 7 ? It is much easier if you are using SQL 2005 or later

KH
[spoiler]Time is always against us[/spoiler]

kjubnoi
Starting Member

3 Posts

Posted - 2013-06-26 : 03:19:58

thank for read my forums, I use sql server 7 because this place use only sql server 7

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-26 : 04:13:03

create a temp table with column rank char(1), output the result of your existing query to a temp table


-- update the rank in temp table
update	t
set	rank	= case 	when 	rn * 100.0 / (select count(*) from #temp) <= 20
			then	'A'
			when	rn * 100.0 / (select count(*) from #temp) <= 50 -- 20 + 30 = 50
			then	'B'
			else	'C'
			end
from
(
	select	*, rn = (select count(*) from #temp x where x.pd_id <= t.pd_id)
	from	#temp t
) t		

-- the final query
select	*,
	seq	= (select count(*) from #temp x where x.rank = t.rank and x.pd_id <= t.pd_id)
from	#temp t

KH
[spoiler]Time is always against us[/spoiler]

kjubnoi
Starting Member

3 Posts

Posted - 2013-06-27 : 03:03:01

thank very much, khtan ^^ and i have a question ... Can we create select statement by use only 1 statement ??? (not use temp table or update)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-27 : 20:48:01

don't think it is possible with SQL Server 7

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources