Copy Cells - 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 2005 Forums

Transact-SQL (2005)

Copy Cells

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2010-01-08 : 08:43:29

Hi

I got following Data
Number Type
1111 123
null 456
null 789
null 147
null 258
null 369
222 100
null 200
null 300
null 400
null 500
null 600
333 951
null 753
null 984
null 651
null 937

Now I want that my Collumn Number does the following thing

1111 123
1111 456
1111 789
1111 147
1111 258
1111 369
222 100
222 200
222 300
222 400
222 500
222 600
333 951
333 753
333 984
333 651
333 937

Is this possible? :)

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-08 : 08:48:00

is there any way of organizing the data in the way you have provided? Unless you can ensure that the data will come back in that exact order everytime there is no way. (basically, is there some ordering column you are not showing us)

EDIT: Since you used the word 'cells' is this more of an excel question? if so then yes there is a formula for that (again if data comes back as is), but wrong forums.

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2010-01-08 : 08:50:43

The left collumn just needs to repeat itsfel until he finds a new number.
So the 1111 keeps on repeating until he finds 222 then this number repeats until it finds something else.

That basicly it :/

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-08 : 08:56:28

Yes, I know, but if your data can not be sorted in any way to get the structure you have above then it is not possible, thats why I was wondering if there was some way that the 1111's were grouped and then the 222's and 333's. If, by chance, your data come back like

333 951
1111 123
null 456
null 789
222 100
null 200
null 300
null 400
null 500
null 600
null 753
null 984
null 651
null 937
null 147
null 258
null 369

(see how the rows are moved) then even though you get a similar result set, their ordering is thrown off and a query will not work, well it will work but the numbers copied over will not be what you expected. So is there some column that does the sorting or grouping that im not seeing?

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2010-01-08 : 08:58:23

The Data is Static, so sorting will never happen.
I Import my Data from a TXT File.
Then i make some Querys on it.
THe only problem is that the left collumn need to be filled too.

Dont mind Sorting or ordering. If i got following :
1111 123
1111 456
1111 789
1111 147
1111 258
1111 369
222 100
222 200
222 300
222 400
222 500
222 600
333 951
333 753
333 984
333 651
333 937

Its perfect :)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-08 : 10:26:59

you are making an incorrect assumption about the way your data is stored.

Even though it has been imported from a TXT file SQL Server makes no guarantees that the data will be stored or retrieved in any sort of order unless you have an ordering column. If you can import the data into table which also has an autonumber column (an IDENTITY for instance) then you can work with it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-08 : 12:31:00

Thanks Charlie for explaining that better, often times I know that something is the way it is, but can not explain WHY very well :)

IBoonz, once you get your data inputed correctly (with some sort of sorting mechanism) then refer to this post on how to Fill the nulls in (look towards the bottom of the post where we have 1's and nulls and fill them in)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137920

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources