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 transpose a table using sql statement

as caption, I want to use sql statement to transponse a table (row to column
and column to row), is this possible and how to script it?
e.g.
1,2,3
4,5,6
(1,2,3) is column name
after transponse, the table become
1,4
2,5
3,6
(1,4) is column name
Thx!!"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
If you're using SQL 2005, look up pivot()
> as caption, I want to use sql statement to transponse a table (row to
column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!|||Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> If you're using SQL 2005, look up pivot()
>
> > as caption, I want to use sql statement to transponse a table (row to
> column
> > and column to row), is this possible and how to script it?
> > e.g.
> > 1,2,3
> > 4,5,6
> > (1,2,3) is column name
> > after transponse, the table become
> > 1,4
> > 2,5
> > 3,6
> > (1,4) is column name
> >
> > Thx!!
>
>|||This is a multi-part message in MIME format.
--020008060109080504060608
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
With SQL Server 2000, you do it with a GROUP BY clause and a series of
<AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your needs.
See http://www.aspfaq.com/2462
--
*mike hodgson*
http://sqlnerd.blogspot.com
kei wrote:
>Oh! I am using SQL2000,is there any method using SQL Server 2000?
>"Greg D. Moore (Strider)" wrote:
>
>>"kei" <kei@.discussions.microsoft.com> wrote in message
>>news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
>>If you're using SQL 2005, look up pivot()
>>
>>
>>as caption, I want to use sql statement to transponse a table (row to
>>
>>column
>>
>>and column to row), is this possible and how to script it?
>>e.g.
>>1,2,3
>>4,5,6
>>(1,2,3) is column name
>>after transponse, the table become
>>1,4
>>2,5
>>3,6
>>(1,4) is column name
>>Thx!!
>>
>>
--020008060109080504060608
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>With SQL Server 2000, you do it with a GROUP BY clause and a series
of <AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your
needs.<br>
<br>
See <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/2462</a></tt><br>">http://www.aspfaq.com/2462">http://www.aspfaq.com/2462</a></tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
kei wrote:
<blockquote cite="mid723F76A5-9F57-42B1-B787-DF454C95361D@.microsoft.com"
type="cite">
<pre wrap="">Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">"kei" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:kei@.discussions.microsoft.com"><kei@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com">news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com</a>...
If you're using SQL 2005, look up pivot()
</pre>
<blockquote type="cite">
<pre wrap="">as caption, I want to use sql statement to transponse a table (row to
</pre>
</blockquote>
<pre wrap="">column
</pre>
<blockquote type="cite">
<pre wrap="">and column to row), is this possible and how to script it?
e.g.
1,2,3
4,5,6
(1,2,3) is column name
after transponse, the table become
1,4
2,5
3,6
(1,4) is column name
Thx!!
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
</body>
</html>
--020008060109080504060608--|||Just curious: why do you want to do this?
What kind of software environment do you work in?|||may be i can help you in this regard.
can u publish some sample data with scripts to create object?
kay
"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> as caption, I want to use sql statement to transponse a table (row to
> column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!

How to Suppress the Hiden Row

Hi Group,
When i rendered the report to MHTML the hidden items are still displaying
and in the preview mode a blank space is displayed.So how do i suppress the
hidden items.When you hide a control you , the control only doesnt show
up but it still holds its position.
So you could actually have the values in a table and then
apply the hidden expression to the column.
In this case the column if its hidden will not show a blank
space.
Hope this helps
>--Original Message--
>Hi Group,
>When i rendered the report to MHTML the hidden items are
still displaying
>and in the preview mode a blank space is displayed.So how
do i suppress the
>hidden items.
>
>.
>

how to subtract value at previous row from current row

Hi all,

I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @. minute 2 plus sales @. minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.

would appreciate any help i can get. thanks

Well, here's one methodology - but it is a bit dangerous if you ever delete records from this table. You will need to add a row identifying column (an auto incrementing number) - in this SQL statement I'm presuming it has a increment of 1.

SELECT (TableA.RowNumValue - TableB.RowNumValue) AS Val

FROM

YourTable TableA

INNER JOIN

YourTable TableB

ON

TableB.ID = TableA.ID - 1

This joins the table on itself, matching the previous record based on the autonumber ID (simply called "ID" here).

Once again, if you ever delete records this methodology won't be accurate. Of course, since you are entering the data "cumulatively", deleting data would be a problem for you anyway.

As a side question: why is it necessary to save the data in a cumulative manner? A database is designed for storage and aggregation - you'll save yourself a lot of headache if you simply store the data point as is, and sum the records up in a sql query.

--
Tony Alicea
http://www.theabstractionpoint.com
clarity of mind and creativity in application software development...

|||

Hi Tony,

Thank you so much for your time and input. I will try the self join again based on the method you showed me. The fisrt time I did, I used another field based on time and tried to manipulate but it didn't work....but your method makes sense
And I agree with you, it really doesn't help at all that the data is stored in this manner. Unfortunately, I have limited say in the design of it and this will probably be the last time I use it. But you are absolutely right.

Hey, thanks again.|||

Ah, trying to query a data structure that you didn't design. I've been there. Well, hope it works. If it does don't forget to mark the thread as answered to close it out.

Let us know how it goes!

how to subtract value at previous row from current row

Hi all,

I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @. minute 2 plus sales @. minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.

would appreciate any help i can get. thanks

Well, here's one methodology - but it is a bit dangerous if you ever delete records from this table. You will need to add a row identifying column (an auto incrementing number) - in this SQL statement I'm presuming it has a increment of 1.

SELECT (TableA.RowNumValue - TableB.RowNumValue) AS Val

FROM

YourTable TableA

INNER JOIN

YourTable TableB

ON

TableB.ID = TableA.ID - 1

This joins the table on itself, matching the previous record based on the autonumber ID (simply called "ID" here).

Once again, if you ever delete records this methodology won't be accurate. Of course, since you are entering the data "cumulatively", deleting data would be a problem for you anyway.

As a side question: why is it necessary to save the data in a cumulative manner? A database is designed for storage and aggregation - you'll save yourself a lot of headache if you simply store the data point as is, and sum the records up in a sql query.

--
Tony Alicea
http://www.theabstractionpoint.com
clarity of mind and creativity in application software development...

|||

Hi Tony,

Thank you so much for your time and input. I will try the self join again based on the method you showed me. The fisrt time I did, I used another field based on time and tried to manipulate but it didn't work....but your method makes sense
And I agree with you, it really doesn't help at all that the data is stored in this manner. Unfortunately, I have limited say in the design of it and this will probably be the last time I use it. But you are absolutely right.

Hey, thanks again.
|||

Ah, trying to query a data structure that you didn't design. I've been there. Well, hope it works. If it does don't forget to mark the thread as answered to close it out.

Let us know how it goes!

How to stop mssql

Hi.
I need to stop mssql service via command row.
How can I do?
Tnk,
Luca"Luca" <NOSPAMprong2k@.hotmail.com> wrote in message
news:chpn2i$rk7$1@.atlantis.cu.mi.it...
> Hi.
> I need to stop mssql service via command row.

net stop mssqlserver

or net stop mssqlserver /y

That will also stop the SQL Server agent w/o prompting.

(net stop sqlserveragent stops the agent.)

> How can I do?
> Tnk,
> Luca