locked
Need output of a table data like this.... RRS feed

  • Question

  • Hi,

    I have a column and the data is in column like:

    ID

    1

    2

    3

    4

    I want to output like:

    ID

    1

    2

    2

    3

    3

    3

    4

    4

    4

    4

    Please let me know how do I do in sql server 2008?

    Thanks in Advance!

    Tuesday, June 25, 2013 11:06 AM

Answers

  • Try this:
    with myTable as (
    	select 1 as id
    	union select 2
    	union select 3
    	union select 4
    )
    
    select mt1.Id
    from myTable mt1
    inner join myTable mt2
    on mt1.id >= mt2.id
    order by mt1.id

    • Marked as answer by Sanjay557 Tuesday, June 25, 2013 2:04 PM
    Tuesday, June 25, 2013 11:36 AM

All replies

  • Try this:
    with myTable as (
    	select 1 as id
    	union select 2
    	union select 3
    	union select 4
    )
    
    select mt1.Id
    from myTable mt1
    inner join myTable mt2
    on mt1.id >= mt2.id
    order by mt1.id

    • Marked as answer by Sanjay557 Tuesday, June 25, 2013 2:04 PM
    Tuesday, June 25, 2013 11:36 AM
  • try this:

    DECLARE @tempTable TABLE ( ID int )
    
    INSERT INTO @tempTable VALUES (1),(2),(3),(4)
    
    SELECT ID=A.ID
      FROM @tempTable as A left outer join @tempTable as B on A.ID >= B.ID
    ORDER BY ID

    Tuesday, June 25, 2013 11:42 AM
  • try this query :

    Select t2.ID  from @tab t1 cross  join @tab t2 where t1.ID < = t2.ID

    Tuesday, June 25, 2013 11:42 AM
  • Try this one:

    CREATE TABLE TEST_IDENT1(ID INT)
    
    INSERT INTO TEST_IDENT1 VALUES(1)
    INSERT INTO TEST_IDENT1 VALUES(2)
    INSERT INTO TEST_IDENT1 VALUES(3)
    INSERT INTO TEST_IDENT1 VALUES(4)
    
    -- CODE
    
    SELECT A.ID 
    FROM TEST_IDENT1 A JOIN TEST_IDENT1 B ON A.ID>=B.ID
    ORDER BY A.ID

    Thanks.


    bala krishna

    Tuesday, June 25, 2013 11:46 AM
  • create table #t (id int)

    insert into #t values (1),(2),(3),(4)


    select number from #t 
    cross apply (select distinct number from master..spt_values
                where number>=id  and number < (select max(id)+1 from #t)) as d
    order by number

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, June 25, 2013 11:49 AM
    Answerer
  • declare

    @table table


    (

    id int


    )


    insert

    into @table values (1),(2),(3),(4)

    ;

    WITH CTE as (


    Select

    id , 1 as ct from  @table


    UNION

    ALL


    Select

    t.id , ct +1   from @table t


    join

    CTE c on t.id = c.id


    Where

    c.id  > ct


    )



    Select

    id from CTE order by id

    Tuesday, June 25, 2013 12:22 PM
  • CREATE TABLE  #test(ID INT)
    INSERT INTO  #test VALUES (1),(2),(3),(4)
    --Create an auxiliary number table
    CREATE TABLE  #num(number INT) 
    INSERT INTO  #num VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
    SELECT t.ID 
    FROM  #test t, #num n ON t.ID>=n.number
    ORDER BY t.ID
    drop table #test
    drop table #num


    • Edited by Jingyang Li Tuesday, June 25, 2013 2:06 PM change alias name
    Tuesday, June 25, 2013 2:04 PM
  • Thanks Jonas and every one for your valuable time to solve my issue. :-)
    Tuesday, June 25, 2013 2:06 PM