Results 1 to 1 of 1
  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Unanswered: How to pivot this case (in MSSQL 2000)

    This is my table and data sample

    --Table
    CREATE TABLE [dbo].[CAN_TmpMealtime](
    [AttTime] [datetime] NOT NULL,
    [MachineID] [int] NOT NULL,
    [EmployeeID] [varchar](20) NOT NULL,
    [AttState] [int] NULL,
    [Shift] [int] NULL,
    [ProcessConvert] [bit] NULL,
    [Process] [bit] NULL,
    CONSTRAINT [PK_CAN_TmpMealtime] PRIMARY KEY CLUSTERED
    (
    [AttTime] ASC,
    [MachineID] ASC,
    [EmployeeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    --Insert data
    INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttSt ate) VALUES('2010-06-16 08:09:41.000','1','A','1')
    INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttSt ate) VALUES('2010-06-16 08:10:41.000','1','A','1')
    INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttSt ate) VALUES('2010-06-17 08:09:41.000','1','A','1')
    INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttSt ate) VALUES('2010-06-17 02:09:41.000','2','A','1')
    INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttSt ate) VALUES('2010-06-17 02:09:41.000','2','A','1')
    INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttSt ate) VALUES('2010-06-16 09:09:41.000','1','B','1')
    INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttSt ate) VALUES('2010-06-16 08:10:41.000','1','B','1')
    INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttSt ate) VALUES('2010-06-17 08:10:41.000','2','B','1')

    EmpID | MachineID | Date | Value
    A 1 2010-06-16 2010-06-16 08:00:00
    A 1 2010-06-16 2010-06-16 09:00:00
    A 1 2010-06-17 2010-06-17 09:00:00
    A 2 2010-06-17 2010-06-17 09:02:00
    A 2 2010-06-17 2010-06-17 09:04:00
    B 1 2010-06-16 2010-06-16 09:04:00
    B 1 2010-06-17 2010-06-17 09:04:00

    I want to display result

    EmpID | MachineID | 2010-06-16 | 2010-06-17
    A 1 | 2010-06-16 08:00:00 | 2010-06-16 09:00:00
    A 1 | 2010-06-16 09:00:00 | NULL
    A 2 | NULL | 2010-06-17 09:02:00
    A 2 | NULL | 2011-06-02 09:04:00
    B 1 | 2011-06-16 09:04:00 | NULL
    B 1 | NULL |2011-06-16 09:04:00


    Can you give me some idea. Thanks.
    Last edited by vua_rua; 12-04-11 at 22:25.

Posting Permissions

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