quote:

---

Originally posted by actsql

I have a table where the designer allowed time reporting for 8 different types of activity on each timecard, then stored the hours by activity in 8 separate columns in the database. Example:

Table A.
Column Headers: Employee|Date|Phase1Hours|Phase2Hours, etc...
Data: Fred Jones| 7/15/13 |3|3, etc...

The problem is there is no way based on this structure to get an employee's hours for the day in columnar form.

To get this data into columnar form I have used Select queries with Union All, for instance:

SELECT Employee, Date, Phase1
FROM Table A

UNION ALL

SELECT Employee, Date, Phase2
FROM Table A

,etc.....

Query is running slow (I am guessing it is because the same table is repeatedly being accessed).

Has anyone run into a similar situation and found a faster more efficient solution?

Thanks.

---

SELECT Employee,Date,Hourse, Phase FROM TableA
UNPIVOT ([Hours] FOR Phase IN ([Phase1Hours],[Phase2Hours],[Phase3Hours]))U;

SELECT *
FROM
(
SELECT Employee,Date FROM TableA
)t
CROSS APPLY (VALUES ('Phase1',[Phase1Hours]),('Phase2',[Phase2Hours]),('Phase3',[Phase3Hours]))t1(Phase,Hours)