Urgent HELP on Converting columns to rows

[Shivakumar]

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

[Dave Hughes]

On Sat, 08 Dec 2007 14:56:49 -0000, Shivakumar <cs****@gmail.comwrote:

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.

[Mark A]

"Dave Hughes" <da**@waveform.plus.com

wrote in message news:op***************@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?

[Shivakumar]

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.