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, GolaI had answer a similar question before. See my replay at view and link table thread on 14 March 2003

ionut|||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|||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

How to test if one col is "like" another?

We have all done the following:
SELECT COUNT(*) FROM TAB1
WHERE TAB1.COL1 LIKE '%mystring%'
I would like to replace the mystring with another column in the same table.
In this case the StreetName is both a separate column AND contained in the
StreetAddress column.
EX. StreetAddress = '123 Main Street, APT 10'
Streetname = 'Main Street'
Here is a non-functioning logical sample:
Select count(*) From Tab1 T1
Where T1.StreetAddress Like %T1.StreetName%
How can I check if one column is contained somewhere in another?
Thanks,
Michael...
WHERE streetaddress LIKE '%'+streetname+'%'
or
...
WHERE CHARINDEX(streetname,streetaddress)>0
David Portas
SQL Server MVP
--|||It is difficult to believe that the answer is so straight-forward!
Thanks!
"David Portas" wrote:

> ...
> WHERE streetaddress LIKE '%'+streetname+'%'
> or
> ...
> WHERE CHARINDEX(streetname,streetaddress)>0
> --
> David Portas
> SQL Server MVP
> --
>

How to store image datatype value to a variable?

I have image type col.
I'm trying to do the following,
DECLARE @.Data varbinary(16)
SET @.Data = (select imageCol from Table1 where id=3)
As image datatype returns varbinary value, so I want to store image col value to a varbinary variable(or any other type variable, eg., varchar). But getting following error,
========================================
Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
========================================
Is there anyway to store image datatype value to a variable?
Cheers.

Try this:
select @.Data=imageCol from Table1 where id=3

|||

mbanavige wrote:

Try this:
select @.Data=imageCol from Table1 where id=3

Thanks a lot.