Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    30

    Exclamation Unanswered: Use column name (field) as row heading in query

    Hi,

    I have a basic table which has a date field & then 5 time fields (time1, time2, etc) assigned to that date. The idea is that there are different periods within a day that a detail has to be recorded. This must happen 5 times within each day.
    The table is fine but i want to diplay the data in a certain way in a query. currently the data look like this:

    DATE, TIME1, TIME2, TIME3, TIME4, TIME5.
    21/07/2009, 04:32, 13:20, 18:45, 21:21, 22:45.
    22/07/2009, 04:30, 13:20, 18:43, 21:20, 22:45.

    I want the data to be displayed like this:

    DATE: 21/07/2009
    TIME1 - 04:32
    TIME2 - 13:20
    TIME3 - 18:45
    TIME4 - 21:21
    TIME5 - 22:45

    I need this to be done within a sql query. Can this be done with a pivot or a cross tab of some sort?

    Regards,

    Shuja

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dabooj
    I want the data to be displayed like this:

    DATE: 21/07/2009
    TIME1 - 04:32
    TIME2 - 13:20
    TIME3 - 18:45
    TIME4 - 21:21
    TIME5 - 22:45
    you cannot easily achieve this layout with SQL, if at all

    besides, customized layout should not be done in SQL

    do it in your programming language (php, perl, whatever)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    30

    Exclamation

    Hi,

    I'm not trying to get a customised layout in a query. It's a bit difficult to show you on here in any great detail.
    But what i want to do is have the row value for the date as the column & the column headers appearing as rows to create a query as i've shown before. So i want the date to be the column value & then the time headers to dislay as row headers & the time value for each one to appear vertically going down rather than horizontally across.
    I thought this might be possible in a pivot but not sure how. Any ideas?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is as close as you're gonna get...
    Code:
    SELECT date
         , 'time1' AS header
         , time1  AS time_value
      FROM daTable
    UNION ALL
    SELECT date
         , 'time2' 
         , time2
      FROM daTable
    UNION ALL
    SELECT date
         , 'time3' 
         , time3
      FROM daTable
    UNION ALL
    SELECT date
         , 'time4' 
         , time4
      FROM daTable
    UNION ALL
    SELECT date
         , 'time5' 
         , time5
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Something like this perhaps ?

    --Create the table and insert values as portrayed in the previous example.
    CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
    GO
    INSERT INTO pvt VALUES (1,4,3,5,4,4);
    INSERT INTO pvt VALUES (2,4,1,5,5,5);
    INSERT INTO pvt VALUES (3,4,3,5,4,4);
    INSERT INTO pvt VALUES (4,4,2,5,5,4);
    INSERT INTO pvt VALUES (5,5,1,5,5,5);
    GO
    --Unpivot the table.
    SELECT VendorID, Employee, Orders
    FROM
    (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM pvt) p
    UNPIVOT
    (Orders FOR Employee IN
    (Emp1, Emp2, Emp3, Emp4, Emp5)
    )AS unpvt;
    GO

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I would slightly alter r937's solution, to make sure the data is presented in the right order.
    Code:
    CREATE TABLE daTable (
    	DATE	char(10) not null,
    	TIME1	char(5),
    	TIME2	char(5),
    	TIME3	char(5),
    	TIME4	char(5),
    	TIME5	char(5)
    )
    INSERT INTO daTable (DATE, TIME1, TIME2, TIME3, TIME4, TIME5) VALUES 
    ('21/07/2009', '04:32', '13:20', '18:45', '21:21', '22:45')
    INSERT INTO daTable (DATE, TIME1, TIME2, TIME3, TIME4, TIME5) VALUES 
    ('22/07/2009', '04:30', '13:20', '18:43', '21:20', '22:45')
    Code:
    select t.time_value
    FROM (
    SELECT 0 as seq_nr
         , date
         , 'Date: ' + date  AS time_value
      FROM daTable
    UNION ALL
    SELECT 1 as seq_nr
         , date
         , 'time1 - ' + time1  AS time_value
      FROM daTable
    UNION ALL
    SELECT 2 as seq_nr
         , date
         , 'time2 - ' + time2
      FROM daTable
    UNION ALL
    SELECT 3 as seq_nr
         , date
         , 'time3 - ' + time3
      FROM daTable
    UNION ALL
    SELECT 4 as seq_nr
         , date
         , 'time4 - ' + time4
      FROM daTable
    UNION ALL
    SELECT 5 as seq_nr
         , date
         , 'time5 - ' + time5
      FROM daTable ) as T
    ORDER BY date, seq_nr
    
    DROP TABLE daTable
    You wouldn't be using char fields for the date and time columns of course. You will also have to convert those columns back to strings in the query. (Using CHARS for those columns made it a bit easier for me.)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •