Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    18

    Unhappy Unanswered: how to transpose col values into row values?

    Hello SQL people,

    I need a way to transpose the values of a column into values in a row in a SqlServer7 table. Here is the problem:

    I have a table employeeattendance.
    Here is the script -

    if exists (select * from sysobjects where id = object_id(N'[dbo].[EmployeeAttendance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[EmployeeAttendance]
    GO

    CREATE TABLE [dbo].[EmployeeAttendance] (
    [EmployeeID] [varchar] (20) NULL ,
    [AttendanceDate] [varchar] (10) NULL ,
    [Status] [char] (1) NULL
    ) ON [PRIMARY]
    GO


    insert into [EmployeeAttendance] Values ( '20001', '2003-03-28' , 'Y')
    insert into [EmployeeAttendance] Values ( '20001', '2003-03-29' , 'N')
    insert into [EmployeeAttendance] Values ( '20001', '2003-03-30' , 'N')
    insert into [EmployeeAttendance] Values ( '20002', '2003-03-28' , 'Y')
    insert into [EmployeeAttendance] Values ( '20002', '2003-03-29' , 'N')


    Now if i say
    select * from employeeattendance
    Output is -
    EmployeeID AttendanceDate Status
    20001 2003-03-28 Y
    20001 2003-03-29 N
    20001 2003-03-30 N
    20002 2003-03-28 Y
    20002 2003-03-29 N

    But i want the output some thing like this-

    20001 2003-03-28 Y 2003-03-29 N 2003-03-30 N
    20002 2003-03-28 Y 2003-03-29 N

    Please, any help would be greatly appreciated. Thanks, Gola

  2. #2
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: how to transpose col values into row values?

    I had answer a similar question before. See my replay at view and link table thread on 14 March 2003

    ionut

  3. #3
    Join Date
    Apr 2003
    Posts
    18

    Re: how to transpose col values into row values?

    Originally posted by ionut calin
    I had answer a similar question before. See my replay at view and link table thread on 14 March 2003

    ionut
    Thanks ionut for reply, but i am using MS sql server 7.0 and i have only one table.

    Thanks once again.

    Gola Munjal

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could do it in your application program, or else use transact-sql

    see
    http://sqlteam.com/item.asp?ItemID=11021
    and
    http://sqlteam.com/item.asp?ItemID=2368

    rudy

Posting Permissions

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