SEO

Dear Friends,

I want to show columns into rows. For example, I have a table,
TIMESHEET, with the following structure:

CREATE TABLE TIMESHEET
(
EMPNO CHAR(3),
WEEKEND_DATE DATE,
SAT SMALLINT,
SUN SMALLINT,
MON SMALLINT,
TUE SMALLINT,
WED SMALLINT,
THU SMALLINT,
FRI SMALLINT
);
INSERT INTO TIMESHEET VALUES ('001','2007-12-07',8,8,8,8,8,8,8);
INSERT INTO TIMESHEET VALUES ('001','2007-12-14',8,8,8,8,8,8,8);
INSERT INTO TIMESHEET VALUES ('001','2007-12-21',8,8,8,8,8,8,8);
INSERT INTO TIMESHEET VALUES ('001','2007-12-28',8,8,8,8,8,8,8);

Expected output :

EMPNO, DAYLY_DATE, HOURS
====== =========== ======
001 12/1/2007 8
001 12/2/2007 8
001 12/3/2007 8

Etc ....

I have a solution using UNION or UNION ALL but would like to avoid
them.

Thank you in advance for your help.

Shiva

Your sample data have all same values "8" from column sat to fri.
It is unclear to know the values of result column "hours" came from
what column of "timesheet" table, from your sample data.
I guessed that from a row
INSERT INTO TIMESHEET VALUES ('001','2007-12-07',8,8,8,8,8,8,8);
the result you want is as following
(I added (Column name) to clarify my guess.)
EMPNO DAYLY_DATE (Column name) HOURS
====== ========== ============= ======
001 12/01/2007 (Sat) 8
001 12/02/2007 (Sun) 8
001 12/03/2007 (Mon) 8
001 12/04/2007 (Tue) 8
001 12/05/2007 (Wed) 8
001 12/06/2007 (Thu) 8
001 12/07/2007 (Fri) 8

If my guess was right, follwing SELECT statement would be a solution.
SELECT empno
, weekend_date - n DAYS AS daily_date
, CASE n
WHEN 6 THEN sat
WHEN 5 THEN sun
WHEN 4 THEN mon
WHEN 3 THEN tue
WHEN 2 THEN wed
WHEN 1 THEN thu
WHEN 0 THEN fri
END AS hours
FROM TimeSheet
, (VALUES 0,1,2,3,4,5,6) AS T(n)
ORDER BY
empno
, weekend_date
, n DESC
;

On Sat, 08 Dec 2007 14:56:49 -0000, Shivakumar <cshiva@gmail.com> wrote:

> Dear Friends,
>
> I want to show columns into rows. For example, I have a table,
> TIMESHEET, with the following structure:
>
> CREATE TABLE TIMESHEET
> (
> EMPNO CHAR(3),
> WEEKEND_DATE DATE,
> SAT SMALLINT,
> SUN SMALLINT,
> MON SMALLINT,
> TUE SMALLINT,
> WED SMALLINT,
> THU SMALLINT,
> FRI SMALLINT
> );
> INSERT INTO TIMESHEET VALUES ('001','2007-12-07',8,8,8,8,8,8,8);
> INSERT INTO TIMESHEET VALUES ('001','2007-12-14',8,8,8,8,8,8,8);
> INSERT INTO TIMESHEET VALUES ('001','2007-12-21',8,8,8,8,8,8,8);
> INSERT INTO TIMESHEET VALUES ('001','2007-12-28',8,8,8,8,8,8,8);
>
> Expected output :
>
> EMPNO, DAYLY_DATE, HOURS
> ====== =========== ======
> 001 12/1/2007 8
> 001 12/2/2007 8
> 001 12/3/2007 8
>
> Etc ....
>
> I have a solution using UNION or UNION ALL but would like to avoid
> them.

Any particular reason you want to avoid a UNION?

Anyway, it's possible to avoid by using a little LATERAL trickery to
explode the daily values into a 7-row table for each row in the original
table. Here's an example. I've revised the original INSERT statement to
check the output works correctly:

CREATE TABLE TIMESHEET (
EMPNO CHAR(3) NOT NULL,
WEEKEND_DATE DATE NOT NULL,
SAT SMALLINT,
SUN SMALLINT,
MON SMALLINT,
TUE SMALLINT,
WED SMALLINT,
THU SMALLINT,
FRI SMALLINT,
CONSTRAINT PK PRIMARY KEY (EMPNO, WEEKEND_DATE)
);

INSERT INTO TIMESHEET VALUES
('001','2007-12-07',1,2,3,4,5,6,7),
('001','2007-12-14',2,3,4,5,6,7,8),
('002','2007-12-07',3,4,5,6,7,8,9),
('002','2007-12-14',4,5,6,7,8,9,10);

SELECT
T.EMPNO,
E.DAILY_DATE,
E.HOURS
FROM
TIMESHEET T
INNER JOIN LATERAL(VALUES
(T.WEEKEND_DATE - 6 DAYS, T.SAT),
(T.WEEKEND_DATE - 5 DAYS, T.SUN),
(T.WEEKEND_DATE - 4 DAYS, T.MON),
(T.WEEKEND_DATE - 3 DAYS, T.TUE),
(T.WEEKEND_DATE - 2 DAYS, T.WED),
(T.WEEKEND_DATE - 1 DAY, T.THU),
(T.WEEKEND_DATE, T.FRI)
) AS E (DAILY_DATE, HOURS)
ON E.DAILY_DATE BETWEEN T.WEEKEND_DATE - 6 DAYS AND T.WEEKEND_DATE;

EMPNO DAILY_DATE HOURS
----- ---------- ------
001 01/12/2007 1
001 02/12/2007 2
001 03/12/2007 3
001 04/12/2007 4
001 05/12/2007 5
001 06/12/2007 6
001 07/12/2007 7
001 08/12/2007 2
001 09/12/2007 3
001 10/12/2007 4
001 11/12/2007 5
001 12/12/2007 6
001 13/12/2007 7
001 14/12/2007 8
002 01/12/2007 3
002 02/12/2007 4
002 03/12/2007 5
002 04/12/2007 6
002 05/12/2007 7
002 06/12/2007 8
002 07/12/2007 9
002 08/12/2007 4
002 09/12/2007 5
002 10/12/2007 6
002 11/12/2007 7
002 12/12/2007 8
002 13/12/2007 9
002 14/12/2007 10

SELECT * FROM TIMESHEET;

EMPNO WEEKEND_DATE SAT SUN MON TUE WED THU FRI
----- ------------ ------ ------ ------ ------ ------ ------ ------
001 07/12/2007 1 2 3 4 5 6 7
001 14/12/2007 2 3 4 5 6 7 8
002 07/12/2007 3 4 5 6 7 8 9
002 14/12/2007 4 5 6 7 8 9 10


Cheers,

Dave.

On Sat, 08 Dec 2007 17:07:35 -0000, Tonkuma <tonkuma@fiberbit.net> wrote:

> Your sample data have all same values "8" from column sat to fri.
> It is unclear to know the values of result column "hours" came from
> what column of "timesheet" table, from your sample data.
> I guessed that from a row
> INSERT INTO TIMESHEET VALUES ('001','2007-12-07',8,8,8,8,8,8,8);
> the result you want is as following
> (I added (Column name) to clarify my guess.)
> EMPNO DAYLY_DATE (Column name) HOURS
> ====== ========== ============= ======
> 001 12/01/2007 (Sat) 8
> 001 12/02/2007 (Sun) 8
> 001 12/03/2007 (Mon) 8
> 001 12/04/2007 (Tue) 8
> 001 12/05/2007 (Wed) 8
> 001 12/06/2007 (Thu) 8
> 001 12/07/2007 (Fri) 8
>
> If my guess was right, follwing SELECT statement would be a solution.
> SELECT empno
> , weekend_date - n DAYS AS daily_date
> , CASE n
> WHEN 6 THEN sat
> WHEN 5 THEN sun
> WHEN 4 THEN mon
> WHEN 3 THEN tue
> WHEN 2 THEN wed
> WHEN 1 THEN thu
> WHEN 0 THEN fri
> END AS hours
> FROM TimeSheet
> , (VALUES 0,1,2,3,4,5,6) AS T(n)
> ORDER BY
> empno
> , weekend_date
> , n DESC
> ;

Yup, that works ... and it's a lot neater than my 'orrible messing around
with LATERAL (similar principal, but probably a lot more efficient).
Should've refresh the newsreader before posting! Anyway, here's the output
of the above query with the altered INSERT statement I used in my original
reply:

EMPNO DAILY_DATE HOURS
----- ---------- ------
001 01/12/2007 1
001 02/12/2007 2
001 03/12/2007 3
001 04/12/2007 4
001 05/12/2007 5
001 06/12/2007 6
001 07/12/2007 7
001 08/12/2007 2
001 09/12/2007 3
001 10/12/2007 4
001 11/12/2007 5
001 12/12/2007 6
001 13/12/2007 7
001 14/12/2007 8
002 01/12/2007 3
002 02/12/2007 4
002 03/12/2007 5
002 04/12/2007 6
002 05/12/2007 7
002 06/12/2007 8
002 07/12/2007 9
002 08/12/2007 4
002 09/12/2007 5
002 10/12/2007 6
002 11/12/2007 7
002 12/12/2007 8
002 13/12/2007 9
002 14/12/2007 10


Cheers,

Dave.

"Dave Hughes" <dave@waveform.plus.com
> wrote in message newsp.t201devcbln7lx@stewie.waveform.plus.com...
>Any particular reason you want to avoid a UNION?

Maybe because the instructor specified that a UNION cannot be used in the
solution?

All,

THANK YOU !

The reason for not using UNION is due to the number of lines my
original SQL has. If I use union or union all, it would be around 7
pages of SQL.


Again, thanks for all your help.