Transposing rows into columns

[Fabio]

Hello,

is there any quite easy solution for the problem of transposing the rows
into the columns? I have the following table with some data in it:

dealer date 09.00 10.00 11.00 12.00 13.00 14.00
-----------------------------------------------------------------
1 2004-10-01 1/1 2/3 3/3 3/4 4/5 0/3
1 2004-10-02 0/1 1/3 1/3 1/4 3/5 1/3

/and so on.../

I'd like to prepare a SELECT query in the stored procedure that will
display this data in the following form:

dealer date hour reservations
------------------------------------------------------------------
1 2004-10-01 09.00 1/1
1 2004-10-01 10.00 2/3
1 2004-10-01 11.00 3/3
1 2004-10-01 12.00 3/4
1 2004-10-01 13.00 4/5
1 2004-10-01 14.00 0/3
1 2004-10-02 09.00 0/1
1 2004-10-02 10.00 1/3
1 2004-10-02 11.00 1/3
1 2004-10-02 12.00 1/4
1 2004-10-02 13.00 3/5
1 2004-10-02 14.00 1/3

Is it possible to do it using some simple solution? I saw some possible
solutions but they are a bit confusing. Any ideas? Thanks in advance.

...:: fabio

[Simon Hayes]

"Fabio" <fabio_in_wawa@to_wez_wywal.poczta.fm> wrote in message
news:MP************************@news.interia.pl...

Hello,

is there any quite easy solution for the problem of transposing the rows
into the columns? I have the following table with some data in it:

dealer date 09.00 10.00 11.00 12.00 13.00 14.00
-----------------------------------------------------------------
1 2004-10-01 1/1 2/3 3/3 3/4 4/5 0/3
1 2004-10-02 0/1 1/3 1/3 1/4 3/5 1/3

/and so on.../

I'd like to prepare a SELECT query in the stored procedure that will
display this data in the following form:

dealer date hour reservations
------------------------------------------------------------------
1 2004-10-01 09.00 1/1
1 2004-10-01 10.00 2/3
1 2004-10-01 11.00 3/3
1 2004-10-01 12.00 3/4
1 2004-10-01 13.00 4/5
1 2004-10-01 14.00 0/3
1 2004-10-02 09.00 0/1
1 2004-10-02 10.00 1/3
1 2004-10-02 11.00 1/3
1 2004-10-02 12.00 1/4
1 2004-10-02 13.00 3/5
1 2004-10-02 14.00 1/3

Is it possible to do it using some simple solution? I saw some possible
solutions but they are a bit confusing. Any ideas? Thanks in advance.

..:: fabio

Rather than write a confusing query, it would probably be better to improve
your table design - do you really have columns called 09:00 etc.? The time
should be part of the data, and by putting it in the table you can write a
much easier query - see below.

I suspect that you might also want to store reservations as integer, not
character data, because that makes it much easier to do calculations, but
that depends on what the numbers mean.

If you really can't change the table design, I suggest you post CREATE TABLE
and INSERT statements that someone else can paste into Query Analyzer to
recreate your table with some sample data - that means we don't have to
guess what data types you have, what the primary key is, etc.

Simon
create table fabio (
dealer int not null,
res_time datetime not null,
reservations char(3) not null,
constraint PK_fabio primary key (dealer, res_time)
)
go

insert into fabio
select 1, '2004-10-01T09:00:00', '1/3'
union all
select 1, '2004-10-01T10:00:00', '2/3'
union all
select 1, '2004-10-01T11:00:00', '3/3'
union all
select 1, '2004-10-01T12:00:00', '3/4'
union all
select 1, '2004-10-01T13:00:00', '4/5'
go

select
dealer,
convert(char(10), res_time, 105) as 'date',
convert(char(5), res_time, 114) as 'hour',
reservations
from
fabio
order by
dealer, 'date', 'hour'
go

drop table fabio
go

[Ross Presser]

On Wed, 29 Sep 2004 19:08:37 +0200, Fabio wrote:

Hello,

is there any quite easy solution for the problem of transposing the rows
into the columns? I have the following table with some data in it:

dealer date 09.00 10.00 11.00 12.00 13.00 14.00
-----------------------------------------------------------------
1 2004-10-01 1/1 2/3 3/3 3/4 4/5 0/3
1 2004-10-02 0/1 1/3 1/3 1/4 3/5 1/3

/and so on.../

I'd like to prepare a SELECT query in the stored procedure that will
display this data in the following form:

dealer date hour reservations
------------------------------------------------------------------
1 2004-10-01 09.00 1/1
1 2004-10-01 10.00 2/3
1 2004-10-01 11.00 3/3
1 2004-10-01 12.00 3/4
1 2004-10-01 13.00 4/5
1 2004-10-01 14.00 0/3
1 2004-10-02 09.00 0/1
1 2004-10-02 10.00 1/3
1 2004-10-02 11.00 1/3
1 2004-10-02 12.00 1/4
1 2004-10-02 13.00 3/5
1 2004-10-02 14.00 1/3

Is it possible to do it using some simple solution? I saw some possible
solutions but they are a bit confusing. Any ideas? Thanks in advance.

..:: fabio

For this specific problem as you describe it, it's not too hard to
construct an appropriate SELECT, if a bit laborious:

SELECT dealer, date, '09.00' AS hour, [09.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '10.00' AS hour, [10.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '11.00' AS hour, [11.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '12.00' AS hour, [12.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '13.00' AS hour, [13.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '14.00' AS hour, [14.00] AS reservations FROM T1

For the sake of ordering, you probably ought to make that a derived table
and wrap it in another SELECT with an ORDER BY clause:

SELECT dealer, date, hour, reservations
FROM (
SELECT dealer, date, '09.00' AS hour, [09.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '10.00' AS hour, [10.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '11.00' AS hour, [11.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '12.00' AS hour, [12.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '13.00' AS hour, [13.00] AS reservations FROM T1
UNION ALL
SELECT dealer, date, '14.00' AS hour, [14.00] AS reservations FROM T1
) AS T2
ORDER BY dealer, date, hour

And if any of your hour columns are nullable, you may need to outer join
this with a table of hours to avoid missing rows.

To do this in *general*, however, i.e. to write a procedure that you can
reuse that can handle this without knowing the columns beforehand, is much
more difficult. It would probably involve dynamic SQL.

Very often, it's better to handle this at the client, not at the server,
e.g. in an Excel PivotTable, an MS-Access Crosstab Query, an OLAP cube, or
whatever is available.