modify datafie

  • Thread starter Thread starter Ning
  • Start date Start date
N

Ning

Hello,
I have a database file like this:
id status time
001 in 8:20
001 out 10:00
001 in 11:00
002 in 7:00
....

I want to modify it to the databse like:
id status1 time1 status2 time2 status3 time3
001 in 8:20 out 10:00 in 11:00
002 in 7:00 null null null null
....

Could anybody tell me how to do this? Your help will be highly
appreciated.

Ning
 
Crosstab queries are not easily done with SQL-Server because it doesn't
offer a direct support like Access do. You can search this newsgroup on
Google with the word « Crosstab » as a start:

http://groups.google.ca/group/micro...q=crosstab&qt_g=1&searchnow=Search+this+group

as well as other groups such as m.p.sqlserver.programming. The following
articles are interesting on this topic:

http://www.ftponline.com/archives/premier/mgznarch/vbpj/2001/10oct01/sqlpro0110/rj0110/rj0110-1.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q175574

http://www.sqlteam.com/item.asp?ItemID=2955


There is also a Pivot Table feature available with ADP but I never used it,
so I cannot tell you more on this.
 
There is a pivotTable feature in MDB and in ADP. It is pretty crappy.

What i really and honestly reccomend-- i am quite passionate about this

Use ANALYSIS SERVICES-- these; by definition allow you to do COLUMNs
and ROWs like what you're looking for.
it is about 1 billion times more powerful than Access crosstabs lol

Or.. wait for SQL 2005 express edition; it will allow you to use the
PIVOT keyword (which is similiar to the transform-- crosstab--
functionality in MDB).

for a simple example; it is possibly to do with derived tables; it
really depends on how many records you're talking about.
most people try to do crosstab type queries; but they're scanning the
table multiple times

if you do it as a derived table; it has decent performance


Select
SUM(Case When Month = '1' Then derived1.TotalSales Else 0) as 'Jan'
SUM(Case When Month = '2' Then derived1.TotalSales Else 0) as 'Feb'
SUM(Case When Month = '3' Then derived1.TotalSales Else 0) as 'Mar'
SUM(Case When Month = '4' Then derived1.TotalSales Else 0) as 'JApr'
SUM(Case When Month = '5' Then derived1.TotalSales Else 0) as 'May'
SUM(Case When Month = '6' Then derived1.TotalSales Else 0) as 'Jun'
SUM(Case When Month = '7' Then derived1.TotalSales Else 0) as 'Jul'
SUM(Case When Month = '8' Then derived1.TotalSales Else 0) as 'Aug'
SUM(Case When Month = '9' Then derived1.TotalSales Else 0) as 'Sep'
SUM(Case When Month = '10' Then derived1.TotalSales Else 0) as 'Oct'
SUM(Case When Month = '11' Then derived1.TotalSales Else 0) as 'Nov'
SUM(Case When Month = '12' Then derived1.TotalSales Else 0) as 'Dec'
FROM
(
Select Month(Orders.OrderDate) as Month, sum(OrderDetails.Extended) as
TotalSales
From orders inner join orderdetails on orders.orderid =
Click to expand...
orderdetails.orderid
) derived1


I hope my syntax is close on that; it's hard to write without havnig
real data and being able to test it

-Aaron
 
Back
Top