Query Help

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

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='' />]

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

SURYA

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

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

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

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

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.

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

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

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.

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.

Hi Mirko Marovic

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

SURYA