Splitting Data

[stathread]

Does anyone have a way of splitting data in half?
Like placing a 1 & 2 in an empty field.

for instance.

Before:
item count
apples 1000
bannanas 2000


After
item count
apples 500
apples 500
bannanas 1000
bannanas 1000

 

[vongrunt]

OK, two Easter quickies:

select item, [count]/2
from myTable
union all
select item, [count]/2
from myTable
order by item

select item, [count]/2
from myTable
cross join
(	select 1 as no union all
	select 2
) blah

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[stathread]

Thank you but what if i dont have a field with the count of how many apples or banannas there are?

 

[vongrunt]

Create "numbers" table holding integers from 1 to say, 1000. The rest is easy...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[stathread]

So I have a field with items and add a column to my table named count. How would i go about placing the count of each item in the count field.

for example

select distinct item,count(*) from my table

item count
cars 200
trucks 350
planes 500

^^ how can i add a field with these values?

 

[vongrunt]

Now I am confused...

Can you post sample input data and exact expected output - as in original post?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[stathread]

Sorry,

Here is the input data

Item

Car
Truck
Car
Bus
Bus
Truck
Car
Car
Truck
Truck

Id like to add a field called Count and input how many there are in the list this way I can split it with your above code.

Item Count

Truck 6
Car 4
Truck 6
Car 4
Bus 2
Bus 2
Truck 6
Car 4
Car 4
Truck 6
Truck 6
Truck 6

 

[stathread]

so the finished output would look like this..

Item Count Split

Truck 6 1
Car 4 2
Truck 6 1
Car 4 2
Bus 2 3
Bus 2 3
Truck 6 1
Car 4 2
Car 4 2
Truck 6 1
Truck 6 1
Truck 6 1

 

[vongrunt]

I see only 4 trucks

This should do it:

select A.item, B.[count]
from myTable A
inner join
(	select item, count(*) as [count]
	from myTable
	group by item
) B
in A.item = B.item

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

I'm not following it again... why Truck gets 1, Car 2 and Bus 3?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[stathread]

I am confusing myself.

 

[stathread]

Ok back on track its still a lil confusing but i used letters this time.

Item Count Split

Truck 6 a
Car 4 c
Truck 6 a
Car 4 c
Bus 2 f
Bus 2 e
Truck 6 a
Car 4 d
Car 4 d
Truck 6 b
Truck 6 b
Truck 6 b

 

[vongrunt]

If there are no other values to uniquely identify row (primary key or whatever) this is mission impossible...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[stathread]

Thanks for all of your help though!