SQL Server Performance

Query Help

Discussion in 'General Developer Questions' started by vsnreddi, Dec 20, 2005.

  1. vsnreddi New Member

    Hi.
    I have a table like this...

    col1
    ----
    abc
    def
    ghi
    jkl
    mno
    pqr
    stu
    --
    --
    --
    upto 40 rows

    in reporting purpose i want to show the output like this

    col1 col2 col3 col4 col5
    ---- ---- --- ---- ---- --- --- ----
    abc def ghi jkl mno

    upto 10 rows i want the output like this and next 10 rows should come in second row and next 10 rows come in thrid row....

    advance thanks






    SURYA

  2. ranjitjain New Member

    try this<br />declare @test table(c1 varchar(20))<br />insert @test values('ABC')<br />insert @test values('DEF')<br />insert @test values('XYZ')<br /><br />declare @var1 varchar(500)<br />set @var1=''<br />select @var1=@var1+''''+c1+''',' from @test<br />select @var1=left(@var1,len(@var1)-1)<br />exec ('SELECT '+@var1)<br /><br />replace @test with your table name[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  3. vsnreddi New Member

    thanks ranjit , i wll try this and let u know...

    SURYA

  4. vsnreddi New Member

    im not getting the output...
    the live table data as like this..

    type cd
    ---- ----
    IBDAG
    IBDAH
    IBDBA
    IBDBL
    IBDBN
    IBDBR
    IBDBW
    IBDCF

    up to n of rows

    i want to show the output like this

    type col1 col2 col3 col10
    --- --- -- -- -- --
    IBD AG AH BA CF
    IBD PH RD SJ SU
    IBD ND NY NF NH
    --
    --
    first 10 columns should display first row and then 10 rows into second rows













    SURYA

  5. Madhivanan Moderator

  6. vsnreddi New Member

    hi madhivannan,
    using the above link i have prepared sp it is displaying the output as


    Id type cd
    -- --- -------------------------------------------------------------------------------------------
    7IBDAG AH BA BL BN BR BW CF CG
    9OBDAE AG AH BA BE BE BL BN BR

    the cd column displaying n of fileds, what i want to display

    Id type cd cd1 cd2 cd3 cd4 till cd10
    -- --- ------ ------ ------- ------- -------- ------
    7IBDAG AH BA BL BN BR
    7OBDAE AG AH BA BE BE

    whate ever the id 7 rows, i should display cd columns as first 10 rows as 10 columns then rest of rowns it display next rows










    SURYA

  7. Madhivanan Moderator

    Where do you want to show these data?

    Madhivanan

    Failing to plan is Planning to fail
  8. vsnreddi New Member

    In report data should display like that, i have tried with report all the ways im not getting, if we do in procedure output we can display easily..

    SURYA

  9. Madhivanan Moderator

    Which Report are you using?
    Crystal Reports supports CrossTab and check if SSRS also supports it

    Madhivanan

    Failing to plan is Planning to fail
  10. vsnreddi New Member

    quote:Originally posted by Madhivanan

    Which Report are you using?
    Crystal Reports supports CrossTab and check if SSRS also supports it

    Madhivanan

    Failing to plan is Planning to fail
    -------------------
    its will support cross tabs, but the data should cotains columns, but in my case the column is displaying all the rows data in a single column..ie the problem...

    ex:
    col1
    -------------------------------------------------------------------
    BA CD DV AD FD GD FG LG MG JD JO ND GF

    I need first 10 rows

    col1 col2 to col10
    ---- ---- -----
    BA Cd FG

    after 10 values, the next values should display in next row...



    SURYA

  11. mmarovic Active Member

    It's better to solve that kind of problem on the client side. It can be done using sql but that would involve cursor and cursors should be avoided unless there is no other solution.
  12. mmarovic Active Member

    There is also solution using table variables (or temp tables) but still better do it on the client.
  13. vsnreddi New Member

    i got the answer using cursors..but without cursors can we do ?

    declare ar_cur cursor for
    select tp,cd from ar
    order by tp

    open ar_cur
    fetch ar_cur into @tp,@cd
    select @ctr = 1,@ctr_row = 1
    while @@sqlstatus =0
    begin
    if @tp <> @prev_tp
    select @prev_tp = @tp

    if @ctr = 1
    begin
    if @tp = @prev_tp
    insert into bh(tp,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,cd9,cd10,slr) values (@tp,@cd,@cd,@cd,@cd,@cd,@cd,@cd,@cd,@cd,@cd,@ctr_row)
    end

    if @ctr=2
    begin
    if @tp=@prev_tp
    update bh set cd2 = @cd where slr = @ctr_row
    end

    if @ctr=3
    begin
    if @tp=@prev_tp
    update bh set cd3 = @cd where slr = @ctr_row
    end

    if @ctr=4
    begin
    if @tp=@prev_tp
    update bh set cd4 = @cd where slr = @ctr_row
    end

    if @ctr=5
    begin
    if @tp=@prev_tp
    update bh set cd5 = @cd where slr = @ctr_row
    end

    if @ctr=6
    begin
    if @tp=@prev_tp
    update bh set cd6 = @cd where slr = @ctr_row
    end

    if @ctr=7
    begin
    if @tp=@prev_tp
    update bh set cd7 = @cd where slr = @ctr_row
    end

    if @ctr=8
    begin
    if @tp=@prev_tp
    update bh set cd8 = @cd where slr = @ctr_row
    end

    if @ctr=9
    begin
    if @tp=@prev_tp
    update bh set cd9 = @cd where slr = @ctr_row
    end

    if @ctr=10
    begin
    if @tp=@prev_tp
    update bh set cd10 = @cd where slr = @ctr_row
    end

    if @ctr<=10
    begin
    select @ctr=@ctr+1,@ctr_row=@ctr_row+1,

    end
    else
    begin
    select @ctr=1,@ctr_row=@ctr_row+1,
    end

    fetch ar_cur into @tp,@cd

    end


    SURYA

  14. mmarovic Active Member

    If you really want to use sql for it here it goes:


    declare @OrderedInput(
    seqNo int identity(1,1) primary key clustered,
    col1 char(3) not null
    )

    Insert into @orderedInput(col1) select col1 from table order by col1

    select t1.col1 as col1,
    t2.col1 as col2,
    ...
    t10.col1 as col10
    from @OrderedInput t1
    left join @orderedInput t2 on t2.seqNo = t1.seqNo
    ...
    left join @OrderedInput t10 on t10.seqNo = If you really want to use sql for it here it goes:


    declare @OrderedInput(
    seqNo int identity(1,1) primary key clustered,
    col1 char(3) not null
    )

    Insert into @orderedInput(col1) select col1 from table order by col1

    select t1.col1 as col1,
    t2.col1 as col2,
    ...
    t10.col1 as col10
    from @OrderedInput t1
    left join @orderedInput t2 on t2.seqNo/10 = t1.seqNo/10
    ...
    left join @OrderedInput t10 on t10.seqNo/10 = t1.seqNo/10
    where
    t1.seqNo % 10 = 1 and
    t2.seqNo % 10 = 2 and
    ...
    t10.seqNo % 10 = 0
    go
    For small number of rows that's good enough.
    For significant number of rows in the table I would use temp table instead and have additional column columnNumber which I would first update to column number (1-10).
    After that I would create and composite clustered index on seqNo and ColumnNumber. Finally I would run insert and select similar to one mentioned above.
  15. mmarovic Active Member

    Actually additional column storing seqNo/10 value would be better. On top of that update can probably be avoided if computed columns are used instead. I guess computed columns are allowed on temp tables. So the index would be on two computed columns mentioned.
  16. vsnreddi New Member

    Hi Mirko Marovic

    i wll try the above and let u know...thanks for your qry

    SURYA

Share This Page