Database

2-Aug-08 18:23

Hello,

I have made pivot table with the following

TRANSFORM sum(cCapacity) AS CountOfITEM_PFX
SELECT jobworker, sum(cCapacity) AS Total
FROM cCapacity
GROUP BY JObWorker
PIVOT contract;

but how do I transfer the result set into a table.

Regards
Pritha

Mycroft Holmes3-Aug-08 16:20

3-Aug-08 16:20

And the query works? Oh are you using Access! Sorry I assumed you were using SQL Server.

The question as such does not make sense, it defeats the purpose of a database if you are going to create a pivot query and then put it into a table, what happens when you get another jobworker, you need another column. It is for this reason that PIVOT queries were invented.

Never underestimate the power of human stupidity
RAH

prithaa3-Aug-08 19:29

3-Aug-08 19:29

Hello,

Thanks for your reply.

But I wanted to have it temporarily in a table and use the table to view in my VC++ project where views are created to view the tables.

But now should assume that a pivot table cannot be transferred to a table.

Regards,
Priya

Mycroft Holmes3-Aug-08 19:37

3-Aug-08 19:37

Ok, your in a database forum talking about putting pivot data into a table. Now it turns out you have a VC++ project you want to get that data for "viewing". I suggest you take the question to the Visual C++ forum where I would presume they will be able to help as I know nothing about VC++.

In VB/C# it is a standard thing to create a connection to get the data (in a datatable) and use the UI to display it.

Never underestimate the power of human stupidity
RAH

prithaa3-Aug-08 20:48

3-Aug-08 20:48

Hello,

Thanks for your replies

But in the database forum I am asking about making pivot tables from a table where I am getting the view of the pivot table and not the 'physical' table.

I don't to make a pivot table in VC++ .I will just display a table (which is pivot table in access or SQL server ) in VC++.

I will be using the connection to the database and display it in VC++.But the table(pivot) will have to be made in SQL server or access.

Thaanks & Regards,
Pritha

Mycroft Holmes3-Aug-08 21:13

3-Aug-08 21:13

I'm having trouble understanding your requirement. These 2 statement seem to contradict each other.

OP

prithaa wrote:
I have made pivot table with the following

prithaa wrote:
I am asking about making pivot tables

and

prithaa wrote:
(which is pivot table in access or SQL server )

this is the MOST relevant thing as pivot is totally different in the 2 databases. I beleive the transorm is an Access specific thing. Either way the pivot is created by a properly constructed select statement which you get via you DAL. Search the messages for Pivot as there have been numerious discussions on it. I beleive there is even an article on Pivot tables in SQL Server.

Never underestimate the power of human stupidity
RAH

prithaa4-Aug-08 0:49

4-Aug-08 0:49

Hello,

Thanks for your reply.

(I have made pivot table with the following)

TRANSFORM sum(cCapacity) AS SumOFCapacity
SELECT jobworker, sum(cCapacity) AS Total
FROM cCapacity
GROUP BY JObWorker
PIVOT contract;

This means that I don't get any table but I get the view of the pivot table but the view of the above query is of no use to me if I cannot transfer the view to a table and use the table later.

I want a table (pivot table) not a view (pivot view)

Regards,
PRitha

Mycroft Holmes4-Aug-08 0:56

4-Aug-08 0:56

Lets keep it simple:
What database are you using?

Never underestimate the power of human stupidity
RAH

prithaa4-Aug-08 1:23

4-Aug-08 1:23

Hello,

thanks

The query that I wrote worked with MS Access but I would like to work on SQL server.I wrote it in SQL server but it did not work and I got the error (undeclared variable pivot).I think there is a version problem with SQL server.

Thanks & regards
Pritha

Mycroft Holmes4-Aug-08 2:20

4-Aug-08 2:20

prithaa wrote:
I think there is a version problem with SQL server

Nope the pivot structure is TOTALLY different in SQL Server. Do the following.

1. Get SQL Server
2. Research and create you pivot select statement in SQL Server. There are a number of posts and articles on CP to help with this
3. create a stored proc to execute the pivot select.
4. connect to sql via your DAL and execute the stored proc
5. use the result set returned to bind to a grid for display (C#/VB)

Never underestimate the power of human stupidity
RAH

prithaa4-Aug-08 6:07

4-Aug-08 6:07

Hello,

Thanks for you replies

I will search for the select in SQL server
but one last question.
If the query works in Access and gives the Pivot view then can I make pivot table in a seperate table.

Pritha

Mycroft Holmes4-Aug-08 14:08

Ado AppendChunk() fucntion did not save data to oracle database

4-Aug-08 14:08

I beleive you can use Select ..... Into NewTableName

Never underestimate the power of human stupidity
RAH

yklim2-Aug-08 0:10

yklim

2-Aug-08 0:10

I have try to use ado AppendChunk() fucntion in vc++6 to save to a clob data type to oracle database, but when i read back, the data was not save to the oracle database. Anyone know what is the problem?

// BEFORE THIS FUNTION, ORACLE DATABASE WAS CONNECTED
bool ADODBHandler::UpdateMap()
{
AdoNS::_RecordsetPtr rst;

try
{
..........................

rst.CreateInstance("ADODB.Recordset");

rst->CursorType = AdoNS::adOpenKeyset;
rst->LockType = AdoNS::adLockOptimistic;

HRESULT hr = rst->Open("pcmap.WAFERMAP3_PROCESSMAP", _variant_t((IDispatch*)m_cpConnection,true), AdoNS::adOpenKeyset, AdoNS::adLockOptimistic, AdoNS::adCmdTable);

rst->AddNew();

rst->Fields->GetItem("LOTNO")->PutValue(lotno);
rst->Fields->GetItem("WAFERNO")->PutValue(WaferNo);
rst->Fields->GetItem("WAFERID")->PutValue(Waferid);
rst->Fields->GetItem("PROBER_FILE")->PutValue(ProberFile);
rst->Fields->GetItem("ORIENT")->PutValue(Orient);
rst->Fields->GetItem("ROW_COUNT")->PutValue(RowCount);
rst->Fields->GetItem("COL_COUNT")->PutValue(ColCount);
rst->Fields->GetItem("PASS")->PutValue(Pass);
rst->Fields->GetItem("FAIL")->PutValue(Fail);
rst->Fields->GetItem("SOURCE")->PutValue(Source);
rst->Fields->GetItem("ET_TIME")->PutValue(date);
rst->Fields->GetItem("MAP_VER")->PutValue(Ver);
rst->Update();

_variant_t varChunk;

varChunk.bstrVal = _bstr_t(cWafer); // cWafer is a char array about 2500 bytes long
// for some strange reasons, cannot save varChunk to database and no exception error was capture
rst->Fields->GetItem("MAP")->AppendChunk(varChunk);
rst->Update();
}
.........
}

conver a code to sql server2000

mehrnoosh1-Aug-08 20:08

mehrnoosh

1-Aug-08 20:08

hi all
how can i convert the code below to sqlserver2000?

USE db1
DECLARE @Definition nvarchar(1000)
SELECT @Definition = definition FROM sys.all_sql_modules INNER JOIN sys.all_objects
ON sys.all_sql_modules.object_id = sys.all_objects.object_id
WHERE sys.all_objects.name = 'YourObjectName'

USE db2
EXEC(@Definition)

Query and sub query problem

Rameez Raja1-Aug-08 19:03

Rameez Raja

1-Aug-08 19:03

Hi every body,

i have reservation table which contains folowing columns

Reserve_ID, vehicle_Name,ReservestartDatetime,Resetrveenddatetime,purpose

i want to write a query that prints records agains each vehicle for suppose one vehicle is reserved two times in day i want to print those records on a single row .

i know i have to use query and sub query concept but how i wrtie a query for that.

else suggest me a good way to do that .

Reagrds
Rameez

Re: Query and sub query problem

Mycroft Holmes3-Aug-08 16:30

3-Aug-08 16:30

Take a look at this thread, leo came up with a good result

clickety[^]

Never underestimate the power of human stupidity
RAH

confusion

netJP12L1-Aug-08 5:29

netJP12L

1-Aug-08 5:29

Hi fellows i am learning about sql in my class and am shacking my head up after recalling about MS SQL 2005 Transaction log files. I have to recover a table that was droped at certain time of inteval from Trans-log file. I opend the file and found out its a binary file you can't really read it easily. How can I reterive a table from Translog file. I remember I ask this in class and my teacher said that look at the time stamp in Transaction log file up to the point you want to recover. Do you fully agree if not any better solution

Thanks

Help With Aggragate MAX

Rafone1-Aug-08 3:02

Rafone

1-Aug-08 3:02

I need to find the max value and the date it happend in a table that has 3 columns. I can get the MAX value but have not been able to get the corrosponding date from the record.
Table structure is...
ID, UTCDateTime, Value

This is the query I've been using to get MAX value
SELECT MAX(value)As MyValue
FROM Tablename

Can some one point me in the right direction??

THX
meh

Syed Mehroz Alam1-Aug-08 3:23

Syed Mehroz Alam

1-Aug-08 3:23

Would be nice to pickup the first record from a sorted view. Something like:

Select Top(1) ID, UTCDateTime, Value 
From TableName
OrderBy Value Desc

Syed Mehroz Alam

My Articles

SomeGuyThatIsMe1-Aug-08 5:00

SomeGuyThatIsMe

1-Aug-08 5:00

if you really want to do it that way you can do

SELECT TOP 1 MAX(value) as MyValue, UTCDateTime
FROM TableName
GROUP BY UTCDateTime

you have to have the top 1 otherwise you will get all of the max values for every dateTime, in descending order..

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Rafone1-Aug-08 8:59

Rafone

1-Aug-08 8:59

Thanks for the replys but....

Both of these examples only return the first record it finds not the largest value

meh