January 23, 2024 at 9:09 am
Hi All
create table #temptable (idValue int)
insert into #temptable values(1)
insert into #temptable values(2)
insert into #temptable values(3)
insert into #temptable values(4)
insert into #temptable values(5)
insert into #temptable values(6)
insert into #temptable values(7)
insert into #temptable values(8)
insert into #temptable values(9)
insert into #temptable values(10)
In this above Query I need two columns output in following manner
declare @setValue int
set @setValue=2
select idvalue From #temptable
idvalue -- pairindex
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4
9 5
10 5
Suppose
declare @setValue int
set @setValue=3
select idvalue,pairindex From #temptable
idvalue -- pairindex
1 1
2 1
3 1
4 2
5 2
6 2
7 3
8 3
9 3
10 4
Suppose @setValue is three means three time should repeat.
January 23, 2024 at 9:19 am
Can you please complete your question for desired results after
set @setValue=3
?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 23, 2024 at 2:32 pm
Just a heads up vs.satheesh - if you EDIT your post, nobody gets notified. So to Phil's, you haven't answered his question yet even though you updated the original post. IF you edit the post, PLEASE post a reply so that people like Phil are able to help you out!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 23, 2024 at 2:57 pm
Mr Brian Gale is correct, I saw no notification. Here is a possible solution – it relies in integer division:
DROP TABLE IF EXISTS #temptable;
CREATE TABLE #temptable
(
idValue INT
);
INSERT #temptable
VALUES
(1)
,(2)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
,(9)
,(10);
DECLARE @setValue INT = 4;
SELECT t.idValue
,1 + (ROW_NUMBER () OVER (ORDER BY t.idValue) - 1) / @setValue
FROM #temptable t;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 24, 2024 at 4:09 am
Thank You. Its working fine
January 31, 2024 at 3:21 pm
You're still writing SQL as if you were in BASIC or Fortran. Let's start off with the create table statement. By definition, and not as an option, table must have a key. Not requiring a key was probably a mistake on our part when we were writing the standards. But our thought was the existing files would be moved from there original languages into SQL and that somehow the programmer would fix it.
Microsoft has been very bad about requiring the terminatingsemi-colon, so people who learned on SQL Server tend to leave it off. But eventually the LALR(1) grammar we deliberately voted into the language caught up with them and it's now required in some of the more advanced features. It's a good idea to learn to write the language instead of the local dialect.
CREATE TABLE Foobar
(foo INTEGER NOT NULL PRIMARY KEY);
Unlike languages based on punch cards, SQL allows the insertion of multiple rows in a single statement. This is also a good idea, not just because you will not look like a hillbilly who can't speak proper SQL, but because the optimizer can make decisions. Remember, this language is called T-SQL because the T stands for "transaction" and it treats every statement as a single transaction. With all the overhead of a transaction! And sequential insertion into the table.
INSERT INTO Foobar
VALUES
(1), (2), (3), (4), (5),
(6), (7), (8), (9), (10);
camelCase is also a measurably bad programming habit. A reader's eye has been trained to jump to the uppercase letter, and then back to the start of the word. There are very good reasons, newspapers and books are not set in uppercase. I had to get into the research when I worked for AIRMICS. The preferred form in ISO is the use of the underscore between words in a compound name. After a few centuries of writing on lined paper, Latin alphabet users have smooth eye flow.
>> In this above Query I need two columns output in following manner
This looks like a job for the NTILE() function. I think there's a recent article on it floating around out here somewhere. Also, T SQL allows an initial value in the declaration of local variables, so there's no need for separate assignment SET statement. Again, your background in BASIC is showing
DECLARE @set_parameter INTEGER (@set_parameter =2);
Please post DDL and follow ANSI/ISO standards when asking for help.
February 1, 2024 at 10:24 pm
You're still writing SQL as if you were in BASIC or Fortran. Let's start off with the create table statement. By definition, and not as an option, table must have a key. Not requiring a key was probably a mistake on our part when we were writing the standards. But our thought was the existing files would be moved from there original languages into SQL and that somehow the programmer would fix it.
Microsoft has been very bad about requiring the terminatingsemi-colon, so people who learned on SQL Server tend to leave it off. But eventually the LALR(1) grammar we deliberately voted into the language caught up with them and it's now required in some of the more advanced features. It's a good idea to learn to write the language instead of the local dialect.
CREATE TABLE Foobar (foo INTEGER NOT NULL PRIMARY KEY);
Unlike languages based on punch cards, SQL allows the insertion of multiple rows in a single statement. This is also a good idea, not just because you will not look like a hillbilly who can't speak proper SQL, but because the optimizer can make decisions. Remember, this language is called T-SQL because the T stands for "transaction" and it treats every statement as a single transaction. With all the overhead of a transaction! And sequential insertion into the table.
INSERT INTO Foobar VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
camelCase is also a measurably bad programming habit. A reader's eye has been trained to jump to the uppercase letter, and then back to the start of the word. There are very good reasons, newspapers and books are not set in uppercase. I had to get into the research when I worked for AIRMICS. The preferred form in ISO is the use of the underscore between words in a compound name. After a few centuries of writing on lined paper, Latin alphabet users have smooth eye flow.
>> In this above Query I need two columns output in following manner
This looks like a job for the NTILE() function. I think there's a recent article on it floating around out here somewhere. Also, T SQL allows an initial value in the declaration of local variables, so there's no need for separate assignment SET statement. Again, your background in BASIC is showing
DECLARE @set_parameter INTEGER (@set_parameter =2);
Heh... you're still writing SQL like a first year rookie, Joe. You should read a good book about it sometime. 😀 Unless you're working with real temp tables, you should use the 2 part naming convention and, according to you in the past, name the table after what the table contains rather than using a table name to express a personal opinion.
Also realize that "foobar" is actually a non-committal and childish spelling of the military term of "FUBAR". If you don't know what that means, then look it up. Even if you already know what it means, stop using it because it is a bit more than just a passive aggressive slam on people.
Also, at the end of it all, you've, once again, provided no working code that actually solves the problem asked in the original post. Since you made the suggestion of NTILE and you actually did take the time to create the code to insert the data, please provide the code that would use NTILE to solve this problem. It looks like you started some code but never got past creating/setting the parameter.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply