data problem

Question

data problem

immaduddinahmed

Ten Centuries

Points: 1375
More actions
July 13, 2015 at 3:24 am

#300064

hi,
my data is looks like this
A-------B
1-------22
5--------6
i want my data looks like this
A-------B
1-------22
2-------0
3-------0
4-------0
5-------6
please help me out
thanks for the help
immad

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23002 More actions · Answer 1

immaduddinahmed (7/13/2015)
hi,
my data is looks like this
A-------B
1-------22
5--------6
i want my data looks like this
A-------B
1-------22
2-------0
3-------0
4-------0
5-------6
please help me out
thanks for the help

There's an awful lot missing from your question that makes it almost impossible to answer. Is this data that's being inserted into or extracted from a table? What are the business rules that you are using? What is the table structure? I'm sure that we can help but we'll need a bit more to go on first please.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You need a numbers table - https://www.simple-talk.com/sql/database-administration/creative-solutions-by-using-a-number-table/, then it's a simple matter of a left join between the numbers table and the table with your data

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

If, as it seems, you're trying to fill the gaps in a sequence, create a numbers table[/url], left join it to your table, and set the value of B in your table to 0 where you get a NULL. For more detailed help, please supply table DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements, required results, and, as has already been requested, a fuller description of your problem.

John

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 4

can any one provide me a query

thanks for the help

immad

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

It's really not hard, it's two tables with am outer join. Read the article I referenced, create the numbers table, left join between the numbers table and your data table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

Never mind... post deleted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

You haven't given us much to go on, so this is an educated guess.

Create your tally table like Gail suggested above. Then you'll need to join it to your base table. Since you haven't provided one, I'll create one like this and insert your rows.

create table #t (

A integer,

B integer)

insert into #t(A, B)

select 1, 22

union all

select 5, 6;

Once you have that, you can join it to the tally table to produce the query results you want.

select t.N A, isnull(#t.B, 0) B

from dbo.Tally t

left outer join #t on t.N = #t.A

where t.N <= (select MAX(a) from #t)

order by t.N;

If you want much more than that, you're going to have to provide us with the table DDL.

Edit: Removed database reference for my tally table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions