0

I have a table as below:

Column1:
1
10
20
30
2
40
50
60
3
70
80
90

Output should be:

Column1 Column2:
10 1
20 1
30 1
40 2
50 2
60 2
70 3
80 3
90 3
Thom A
  • 88,727
  • 11
  • 45
  • 75
Avi
  • 11
  • 2
  • Does this answer your question? [Split one column to multiple columns but data will vary SQL](https://stackoverflow.com/questions/13588800/split-one-column-to-multiple-columns-but-data-will-vary-sql) – Mark Schultheiss Jun 15 '22 at 12:01
  • 2
    Do you have any other columns that denote the order of your data, and what should be in Column1 and what in Column2? If not, what you ask is impossible. – Thom A Jun 15 '22 at 12:02
  • How do you know whether `2` comes before `40` or before a different row? Remember that tables are unordered sets – Charlieface Jun 15 '22 at 12:14
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Jun 15 '22 at 14:54

1 Answers1

1

your data

CREATE TABLE mytable(
   Column1 INTEGER  NOT NULL 
  
);
INSERT INTO mytable(Column1) VALUES (1);
INSERT INTO mytable(Column1) VALUES (10);
INSERT INTO mytable(Column1) VALUES (20);
INSERT INTO mytable(Column1) VALUES (30);
INSERT INTO mytable(Column1) VALUES (2);
INSERT INTO mytable(Column1) VALUES (40);
INSERT INTO mytable(Column1) VALUES (50);
INSERT INTO mytable(Column1) VALUES (60);
INSERT INTO mytable(Column1) VALUES (3);
INSERT INTO mytable(Column1) VALUES (70);
INSERT INTO mytable(Column1) VALUES (80);
INSERT INTO mytable(Column1) VALUES (90);

use NTILE in sql server it acts as Tile and hence solve your issue.

SELECT *
FROM   (SELECT column1,
               Ntile (3)
                 OVER (
                   ORDER BY (SELECT NULL) ) Column2
        FROM   mytable) m
WHERE  column1 <> column2

dbfiddle

RF1991
  • 2,037
  • 4
  • 8
  • 17