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.