SQL Query to convert columns to rows

Question

I have a table say Timesheet, which is having one week hour entry stored as a row. Eg:

ID  Start Date  Sun  Mon  Tue  Wed  Thu  Fri  Sat
-------------------------------------------------
1   14-Oct-2012  0    1    1    2    3    5    0

The start date is always Sunday's date. I need to export this data into multiple rows, one row for each date, based on input criteria.

For example, if the input criteria is: Date is between 15-Oct-2012 to 17-Oct-2012, my output should be something like:

Date   Hours
------------
15-Oct   1
16-Oct   1
17-Oct   2

I am using SQL Server 2000, please suggest me a way.

See [Pivot using SQL Server 2000](http://stackoverflow.com/questions/312861/pivot-using-sql-server-2000) — Spevy, Oct 15 '12 at 20:07

score 2 · Accepted Answer · answered Oct 15 '12 at 20:33

SQL Server 2000, does not have an UNPIVOT function so you can use a UNION ALL to query this data and get it in the format that you want:

select date, hours
from
(
  select id, startdate date, sun hours
  from yourtable
  union all
  select id, dateadd(dd, 1, startdate), mon
  from yourtable
  union all
  select id, dateadd(dd, 2, startdate), tue
  from yourtable
  union all
  select id, dateadd(dd, 3, startdate), wed
  from yourtable
  union all
  select id, dateadd(dd, 4, startdate), thu
  from yourtable
  union all
  select id, dateadd(dd, 5, startdate), fri
  from yourtable
  union all
  select id, dateadd(dd, 6, startdate), sat
  from yourtable
) x
where date between '2012-10-15' and '2012-10-17'

See SQL Fiddle With Demo

score 0 · Answer 2 · answered Oct 15 '12 at 20:14

This is how you can get a listing of all columns in a table:

SELECT c.colid, c.name
FROM syscolumns c, sysobjects t
WHERE c.id = t.id
AND t.name = <YOUR_TABLE>
AND t.xtype = 'U'
ORDER BY c.colid;

You can declare that as a subquery and join to it and output whichever colums you want.

SQL Query to convert columns to rows

2 Answers2