Results 1 to 3 of 3

Thread: SQL question

  1. #1
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443

    Unanswered: SQL question

    My sql powers are degrading day by day

    Have a quick question..

    Have a table with 2 columns.

    1, Value1
    2, Value2
    3, Value3
    ....
    30, Value30
    ...

    When I display I need to display it in a column format. to save space...

    i.e.

    1, Value1 11, Value11
    2, Value2 12, Value12
    3, Value3 13, Value13
    .... ......
    10,Value10 20, Value20

    Any help appreciated

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Can I do it with case?

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Formatting output is bets done in your app

    But assuming no gaps in seq num
    Code:
    create table #t1 (c1 int identity,c2 varchar(10))
    insert into #t1 select 
    'val1' union all select
    'val2' union all select
    'val3' union all select
    'val4' union all select
    'val5' union all select
    'val6' union all select
    'val7' union all select
    'val8' union all select
    'val9' union all select
    'val10' union all select
    'val11' union all select
    'val12' union all select
    'val13'
    
    select * from #t1 a, #t1 b
    where a.c1%2=1 and b.c1%2=0
      and a.c1*=b.c1-1
    
    
    declare @cnt int
    select @cnt=convert(int,max(c1)/2.+.5) from #t1
    
    select * from #t1 a, #t1 b
    where a.c1*=b.c1-@cnt
      and a.c1<=@cnt
    
    select 
     'a.c1'=max(case when c1<=@cnt then c1 end)
    ,'a.c2'=max(case when c1<=@cnt then c2 end)
    ,'b.c1'=max(case when c1>@cnt then c1 end)
    ,'b.c2'=max(case when c1>@cnt then c2 end)
    from #t1
    group by c1%@cnt
    order by 1
    
    
    c1          c2         c1          c2         
    ----------- --         ----------- --         
              1 val1                 2 val2       
              3 val3                 4 val4       
              5 val5                 6 val6       
              7 val7                 8 val8       
              9 val9                10 val10      
             11 val11               12 val12      
             13 val13             NULL NULL       
    
    c1          c2         c1          c2         
    ----------- --         ----------- --         
              1 val1                 8 val8       
              2 val2                 9 val9       
              3 val3                10 val10      
              4 val4                11 val11      
              5 val5                12 val12      
              6 val6                13 val13      
              7 val7              NULL NULL       
    
    a.c1        a.c2       b.c1        b.c2       
    ----------- ----       ----------- ----       
              1 val1                 8 val8       
              2 val2                 9 val9       
              3 val3                10 val10      
              4 val4                11 val11      
              5 val5                12 val12      
              6 val6                13 val13      
              7 val7              NULL NULL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •