Convert 1-column result set to 3 columns

I have a one-column result from a SELECT statement that I wish to
spread out into three columns, i.e.
Col
--
A
B
C
D
E
Turns into:
C1 C2 C3
-- -- --
A B C
D E
There is no identity column so I couldn't try using MOD() to come up
with a solution -- any ideas how this might be possible, preferably not
using a temp table?What are the criteria for this denormalisation - i.e. how do you determine
which values belong in which column?
What are you trying to achieve (on a more global scale)? It would help if
you could give us more information.
Also google for "cross-tab query".
ML
http://milambda.blogspot.com/|||ML wrote:
> What are the criteria for this denormalisation - i.e. how do you determine
> which values belong in which column?
There is no extra logic, I simply need to put a set of 1-column data
into three columns from left to right, top to bottom.

> What are you trying to achieve (on a more global scale)? It would help if
> you could give us more information.
The reason is that I am designing a report using SQL Reporting Services
where I need to put data in three columns across the page. Reporting
Services supports 'table' element which displays results in rows.
However each of my result is quite narrow so I need to fit 3 of them in
one row to save paper. And because this is not possible using report
designer, I decided to do it at SQL side.|||I've come up with a solution which uses temp table -- it works, but
since my team thinks temp table is absolutely evil it'd be great if
this can be converted into a more streamlined version!
-- Create data table --
drop table t
create table t
(
C char NOT NULL
)
insert into t values('A')
insert into t values('B')
insert into t values('C')
insert into t values('D')
insert into t values('E')
insert into t values('F')
insert into t values('G')
insert into t values('H')
select * from t
-- using temp table to reorganize result
drop table #tt1
create table #tt1
(
id int identity,
c char not null
)
-- Data
insert into #tt1 select c from t
-- Helper data used for incomplete last row
insert into #tt1 (c) values (' ')
insert into #tt1 (c) values (' ')
insert into #tt1 (c) values (' ')
-- convert one-column table into a three-column one
SELECT t1.c, t2.c, t3.c
from #tt1 t1
join #tt1 t2 on t2.id=t1.id+1
join #tt1 t3 on t3.id=t2.id+1 or (t2.id = ' ' and t3.id=' ') or (t3.id
= ' ')
where
t1.id % 3 = 1
and t2.id % 3 = 2
and t3.id % 3 = 0|||"Anything shall be evil in hands of the evil."
-- less known Apostle
If your team thinks using temporary tables is evil, then what do they say
about the fact that you're denormalizing data purely for formatting purposes
?
As long as the temporary tables are dropped after use and global (##)
temporary tables are used only when several processes use the same data, and
if there is a sufficient contextual isolation of these processes, then there
is nothing evil about temporary tables. Is an AK-47 evil by itself?
ML
http://milambda.blogspot.com/|||Can there be duplicates in the values?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try,
create table dbo.t (
C char NOT NULL
)
insert into dbo.t values('A')
insert into dbo.t values('B')
insert into dbo.t values('C')
insert into dbo.t values('D')
insert into dbo.t values('E')
insert into dbo.t values('F')
insert into dbo.t values('G')
insert into dbo.t values('H')
go
select
max(
case when rank % 3 = 1 then c end
) as c1,
max(
case when rank % 3 = 2 then c end
) as c2,
max(
case when rank % 3 = 0 then c end
) as c3
from
(
select
count(*) - ((count(*) - 1) % 3) as pk,
count(*) as rank,
t1.c
from
dbo.t as t1
inner join
dbo.t as t2
on t2.c <= t1.c
group by
t1.c
) as a
group by
pk
order by
pk
go
drop table dbo.t
go
AMB
"ak199" wrote:

> I have a one-column result from a SELECT statement that I wish to
> spread out into three columns, i.e.
> Col
> --
> A
> B
> C
> D
> E
> Turns into:
> C1 C2 C3
> -- -- --
> A B C
> D E
> There is no identity column so I couldn't try using MOD() to come up
> with a solution -- any ideas how this might be possible, preferably not
> using a temp table?
>