I have Pivot Table query in MS Access that I would like to run in SQL Server 2005. Can anyone help me to convert the syntax over to SQL Server. The following is the query in MS Access: -
TRANSFORM SUM(ps.psScheduled)
-
SELECT pi.piMaxTurns,
-
pi.piAMP,
-
pi.piTons,
-
'',
-
'',
-
ps.psPipe
-
FROM PipeScheduling ps
-
INNER JOIN
-
PipeInfo pi
-
ON ps.psPipe = pi.piPipe
-
AND ps.psMachine = pi.piMachine
-
AND ps.psPlant = pi.piPlant
-
WHERE ps.psPlant = 'W'
-
AND ps.psMachine = '1'
-
AND ps.psDate BETWEEN
-
#12/17/2007#
-
AND
-
#12/23/2007#
-
GROUP BY ps.psPipe,
-
pi.piSequence,
-
pi.piMaxTurns,
-
pi.piAMP,
-
pi.piTons
-
ORDER BY pi.piSequence
-
PIVOT ps.psDate
18  4510 
This is what I've come up with so far. It is still alitte slow. Any advice? -
SELECT pi.piMaxTurns,
-
pi.piAMP,
-
pi.piTons,
-
'',
-
'',
-
ps.psPipe,
-
SUM(CASE WHEN psDate = '1/7/2008' THEN ps.psScheduled END) AS Col1,
-
SUM(CASE WHEN psDate = '1/8/2008' THEN ps.psScheduled END) AS Col2,
-
SUM(CASE WHEN psDate = '1/9/2008' THEN ps.psScheduled END) AS Col3,
-
SUM(CASE WHEN psDate = '1/10/2008' THEN ps.psScheduled END) AS Col4,
-
SUM(CASE WHEN psDate = '1/11/2008' THEN ps.psScheduled END) AS Col5,
-
SUM(CASE WHEN psDate = '1/12/2008' THEN ps.psScheduled END) AS Col6,
-
SUM(CASE WHEN psDate = '1/13/2008' THEN ps.psScheduled END) AS Col7,
-
pi.piSequence
-
FROM fpc44.PipeScheduling ps
-
INNER JOIN
-
fpc44.PipeInfo pi
-
ON ps.psPlant = pi.piPlant
-
AND ps.psMachine = pi.piMachine
-
AND ps.psPipe = pi.piPipe
-
WHERE ps.psDate BETWEEN
-
'1/7/2008'
-
AND
-
'1/13/2008'
-
AND ps.psPlant = 'W'
-
AND ps.psMachine = '1'
-
GROUP BY ps.psPipe,
-
pi.piMaxTurns,
-
pi.piAMP,
-
pi.piTons,
-
pi.piSequence
-
ORDER BY pi.piSequence
-
Does anyone know a better way of doing this?
ck9663 2,878
Recognized Expert Specialist
It would be faster for me to build your query if I'm looking at your table that you want to pivot and the the desired result.If you can post some sample data, it would be better.
-- CK
The following is PipeInfo for plant "A" and PipeScheduling for one week for plant "A".
PipeInfo:
piPlant,piMachi ne,piPipe,piSeq uence,piMaxTurn s,piAMP,piTons
A,1,153CPB,1,,, 0.508
A,1,154CPB,2,,, 0.508
A,1,155CPB,3,,, 0.508
A,1,183CPB,4,50 ,,0.671
A,1,184CPB,5,,, 0.671
A,1,185CPB,6,,, 0.671
A,1,243CPB,7,50 ,,1.0605
A,1,244CPB,8,,, 1.0605
A,1,245CPB,9,,, 1.0605
A,1,303CPB,10,, ,1.535
A,1,304CPB,11,, ,1.535
A,1,305CPB,12,, ,1.535
A,1,363CPB,13,5 0,,2.0945
A,1,364CPB,14,, ,2.0945
A,1,365CPB,15,, ,2.0945
A,1,423CPB,16,, ,2.739
A,1,424CPB,17,, ,2.739
A,1,425CPB,18,, ,2.739
A,1,483CPB,19,, ,3.469
A,1,484CPB,20,, ,3.469
A,1,485CPB,21,, ,3.469
PipeScheduling:
psPlant,psDate, psMachine,psPip e,psScheduled,p sPoured
A,1/28/2008,1,153CPB,0 ,0
A,1/28/2008,1,154CPB,0 ,0
A,1/28/2008,1,155CPB,0 ,0
A,1/28/2008,1,183CPB,0 ,0
A,1/28/2008,1,184CPB,0 ,0
A,1/28/2008,1,185CPB,0 ,0
A,1/28/2008,1,243CPB,0 ,0
A,1/28/2008,1,244CPB,0 ,0
A,1/28/2008,1,245CPB,0 ,0
A,1/28/2008,1,303CPB,0 ,0
A,1/28/2008,1,304CPB,0 ,0
A,1/28/2008,1,305CPB,0 ,0
A,1/28/2008,1,363CPB,0 ,0
A,1/28/2008,1,364CPB,0 ,0
A,1/28/2008,1,365CPB,0 ,0
A,1/28/2008,1,423CPB,0 ,0
A,1/28/2008,1,424CPB,0 ,0
A,1/28/2008,1,425CPB,0 ,0
A,1/28/2008,1,483CPB,0 ,0
A,1/28/2008,1,484CPB,0 ,0
A,1/28/2008,1,485CPB,0 ,0
A,1/29/2008,1,153CPB,1 20,0
A,1/29/2008,1,154CPB,0 ,0
A,1/29/2008,1,155CPB,0 ,0
A,1/29/2008,1,183CPB,0 ,0
A,1/29/2008,1,184CPB,0 ,0
A,1/29/2008,1,185CPB,0 ,0
A,1/29/2008,1,243CPB,6 0,56
A,1/29/2008,1,244CPB,0 ,0
A,1/29/2008,1,245CPB,0 ,0
A,1/29/2008,1,303CPB,3 0,28
A,1/29/2008,1,304CPB,0 ,0
A,1/29/2008,1,305CPB,0 ,0
A,1/29/2008,1,363CPB,0 ,0
A,1/29/2008,1,364CPB,0 ,0
A,1/29/2008,1,365CPB,0 ,0
A,1/29/2008,1,423CPB,0 ,0
A,1/29/2008,1,424CPB,0 ,0
A,1/29/2008,1,425CPB,0 ,0
A,1/29/2008,1,483CPB,0 ,0
A,1/29/2008,1,484CPB,0 ,0
A,1/29/2008,1,485CPB,0 ,0
A,1/30/2008,1,153CPB,1 20,4
A,1/30/2008,1,154CPB,0 ,0
A,1/30/2008,1,155CPB,0 ,0
A,1/30/2008,1,183CPB,0 ,80
A,1/30/2008,1,184CPB,0 ,0
A,1/30/2008,1,185CPB,0 ,0
A,1/30/2008,1,243CPB,6 0,56
A,1/30/2008,1,244CPB,0 ,0
A,1/30/2008,1,245CPB,0 ,0
A,1/30/2008,1,303CPB,3 0,27
A,1/30/2008,1,304CPB,0 ,0
A,1/30/2008,1,305CPB,0 ,0
A,1/30/2008,1,363CPB,0 ,0
A,1/30/2008,1,364CPB,0 ,0
A,1/30/2008,1,365CPB,0 ,0
A,1/30/2008,1,423CPB,0 ,0
A,1/30/2008,1,424CPB,0 ,0
A,1/30/2008,1,425CPB,0 ,0
A,1/30/2008,1,483CPB,0 ,0
A,1/30/2008,1,484CPB,0 ,0
A,1/30/2008,1,485CPB,0 ,0
A,1/31/2008,1,153CPB,1 20,72
A,1/31/2008,1,154CPB,0 ,0
A,1/31/2008,1,155CPB,0 ,0
A,1/31/2008,1,183CPB,0 ,0
A,1/31/2008,1,184CPB,0 ,0
A,1/31/2008,1,185CPB,0 ,0
A,1/31/2008,1,243CPB,6 0,38
A,1/31/2008,1,244CPB,0 ,0
A,1/31/2008,1,245CPB,0 ,0
A,1/31/2008,1,303CPB,3 0,19
A,1/31/2008,1,304CPB,0 ,0
A,1/31/2008,1,305CPB,0 ,0
A,1/31/2008,1,363CPB,0 ,0
A,1/31/2008,1,364CPB,0 ,0
A,1/31/2008,1,365CPB,0 ,0
A,1/31/2008,1,423CPB,0 ,0
A,1/31/2008,1,424CPB,0 ,0
A,1/31/2008,1,425CPB,0 ,0
A,1/31/2008,1,483CPB,0 ,0
A,1/31/2008,1,484CPB,0 ,0
A,1/31/2008,1,485CPB,0 ,0
A,2/1/2008,1,153CPB,1 20,104
A,2/1/2008,1,154CPB,0 ,0
A,2/1/2008,1,155CPB,0 ,0
A,2/1/2008,1,183CPB,0 ,0
A,2/1/2008,1,184CPB,0 ,0
A,2/1/2008,1,185CPB,0 ,0
A,2/1/2008,1,243CPB,6 0,52
A,2/1/2008,1,244CPB,0 ,0
A,2/1/2008,1,245CPB,0 ,0
A,2/1/2008,1,303CPB,3 0,26
A,2/1/2008,1,304CPB,0 ,0
A,2/1/2008,1,305CPB,0 ,0
A,2/1/2008,1,363CPB,0 ,0
A,2/1/2008,1,364CPB,0 ,0
A,2/1/2008,1,365CPB,0 ,0
A,2/1/2008,1,423CPB,0 ,0
A,2/1/2008,1,424CPB,0 ,0
A,2/1/2008,1,425CPB,0 ,0
A,2/1/2008,1,483CPB,0 ,0
A,2/1/2008,1,484CPB,0 ,0
A,2/1/2008,1,485CPB,0 ,0
A,2/2/2008,1,153CPB,0 ,0
A,2/2/2008,1,154CPB,0 ,0
A,2/2/2008,1,155CPB,0 ,0
A,2/2/2008,1,183CPB,0 ,0
A,2/2/2008,1,184CPB,0 ,0
A,2/2/2008,1,185CPB,0 ,0
A,2/2/2008,1,243CPB,0 ,0
A,2/2/2008,1,244CPB,0 ,0
A,2/2/2008,1,245CPB,0 ,0
A,2/2/2008,1,303CPB,0 ,0
A,2/2/2008,1,304CPB,0 ,0
A,2/2/2008,1,305CPB,0 ,0
A,2/2/2008,1,363CPB,0 ,0
A,2/2/2008,1,364CPB,0 ,0
A,2/2/2008,1,365CPB,0 ,0
A,2/2/2008,1,423CPB,0 ,0
A,2/2/2008,1,424CPB,0 ,0
A,2/2/2008,1,425CPB,0 ,0
A,2/2/2008,1,483CPB,0 ,0
A,2/2/2008,1,484CPB,0 ,0
A,2/2/2008,1,485CPB,0 ,0
A,2/3/2008,1,153CPB,0 ,0
A,2/3/2008,1,154CPB,0 ,0
A,2/3/2008,1,155CPB,0 ,0
A,2/3/2008,1,183CPB,0 ,0
A,2/3/2008,1,184CPB,0 ,0
A,2/3/2008,1,185CPB,0 ,0
A,2/3/2008,1,243CPB,0 ,0
A,2/3/2008,1,244CPB,0 ,0
A,2/3/2008,1,245CPB,0 ,0
A,2/3/2008,1,303CPB,0 ,0
A,2/3/2008,1,304CPB,0 ,0
A,2/3/2008,1,305CPB,0 ,0
A,2/3/2008,1,363CPB,0 ,0
A,2/3/2008,1,364CPB,0 ,0
A,2/3/2008,1,365CPB,0 ,0
A,2/3/2008,1,423CPB,0 ,0
A,2/3/2008,1,424CPB,0 ,0
A,2/3/2008,1,425CPB,0 ,0
A,2/3/2008,1,483CPB,0 ,0
A,2/3/2008,1,484CPB,0 ,0
A,2/3/2008,1,485CPB,0 ,0
ck9663 2,878
Recognized Expert Specialist
And how do you want your result?
-- CK
pi.piAMP,
pi.piTons,
'',
'',
ps.psPipe,
psDate(0),
psDate(1),
psDate(2),
psDate(3),
psDate(4),
psDate(5),
psDate(6)
ck9663 2,878
Recognized Expert Specialist
Based on the sample you posted. How would your pivoted table would look like?
-- CK
Scheduled:
,,0.508,,,153CP B,,,,
,,0.508,,,154CP B,,,,
,,0.508,,,155CP B,,,,
,,0.671,200,,18 3CPB,160,160,16 0,160
,,0.671,,,184CP B,,,,
,,0.671,,,185CP B,,,,
,,1.0605,100,,2 43CPB,,,,
,,1.0605,,,244C PB,,,,
,,1.0605,,,245C PB,,,,
,,1.535,,,303CP B,,,,
,,1.535,,,304CP B,,,,
,,1.535,,,305CP B,,,,
,,2.0945,50,,36 3CPB,80,80,80,8 0
,,2.0945,,,364C PB,,,,
,,2.0945,,,365C PB,,,,
,,2.739,,,423CP B,,,,
,,2.739,,,424CP B,,,,
,,2.739,,,425CP B,,,,
Poured:
,,0.508,,,153CP B,,,,
,,0.508,,,154CP B,,,,
,,0.508,,,155CP B,,,,
,,0.671,200,,18 3CPB,136,128,10 4,87
,,0.671,,,184CP B,,,,
,,0.671,,,185CP B,,,,
,,1.0605,100,,2 43CPB,,,,
,,1.0605,,,244C PB,,,,
,,1.0605,,,245C PB,,,,
,,1.535,,,303CP B,,,,
,,1.535,,,304CP B,,,,
,,1.535,,,305CP B,,,,
,,2.0945,50,,36 3CPB,62,62,22,1 2
,,2.0945,,,364C PB,,,22,23
,,2.0945,,,365C PB,,,,
,,2.739,,,423CP B,,,,
,,2.739,,,424CP B,,,,
,,2.739,,,425CP B,,,,
ck9663 2,878
Recognized Expert Specialist
You lost me here.....
You have - PipeInfo:
-
piPlant,piMachine,piPipe,piSequence,piMaxTurns,piA MP,piTons
-
-
PipeScheduling:
-
psPlant,psDate,psMachine,psPipe,psScheduled,psPour ed
How would your pivot table look like?
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Charles Law |
last post by:
I have posted this on a couple of Access and SQL Server newsgroups, but with
no answer yet. There seems to be very little activity on the ones I have
looked at, so I thought I would post here as well, since I have no doubt
that many people here also use Access/SQL Server. Anyway, enough of my
rambling and on with the problem/question.
I have two queries that I use in Access:
<qrySelect>
SELECT table2.column1, table2.column2,...
|
by: Brian Andrus |
last post by:
Ok. I upgraded to MS Access 2003 recently and now I am having great
heartache.
In Access 2002, when I opened a table to view the data, there were
wonderful little "plus" signs that I could click on for each record
that would open up the corresponding record in any linked table. I
could drill down and edit/add records in a most efficient and simple
manner.
In Access 2003, this is GONE! ARGHH!
|
by: Jean |
last post by:
Hi,
I am using an Access 2000 front-end to an Oracle 9 backend.
I want to write a query that returns all records that are not older
than one year for Column "Status_30" (which is a Date).
When I look at the ODBC Datasource in Table DWHADMIN_V_PROBLEM , the
Date formatting looks normal to me, like #05/07/2005#. However, when I
try using the following in my Access Query :
|
by: Jerome Ranch |
last post by:
Okay
So I've got pivot tables setup in Access 2003.
Only about 30K records in the current 2005 databases...the pivots
summarize the info in a number of nice ways.
I need to get the pivot tables into a document so I can distribute the
tables, as is, as an electronic report (without the detailed data)
So I export to rtf and xls, and I get an error that there is a too
much information. I don't want all the data, just the summary table!
|
by: dotnetguru |
last post by:
Hi SMART GUYS,
Please help me write a query. Actually I want to convert my rows into columns. Can anyone kindly give me the query to do it? My rows are about employees. There can be any number of employees (in the following table I have 3 employees ABC, EFG, WRI) with any number of records for each employee (there can be 20, 30 records each), but all employees have the same number of records (in the following table each employee has 3...
| | |
by: Nassa |
last post by:
Hi everyone,
I have 3 tables:
Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int),
PK:id
TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id
Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk
|
by: wujtehacjusz |
last post by:
Dear All
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
I would be very grateful for any help with this.
|
by: retroviz |
last post by:
Hello and thankyou in advance for your help :)
I am developing a sales forcasting application that allows users to enter expected prices for different products by month.
I have about six months to plan this so am just doing some preliminary investigation now.
My database (which will be SQL Server) should have as a minimum, 2 tables; "Products" to store information relating to products and "Prices" to store the prices for each product.
...
|
by: mnarewec |
last post by:
Team here is my SQL Pivot view. I want to convert to MS Access View. Please assist
USE
GO
/****** Object: View . Script Date: 10/17/2008 20:41:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW .
AS
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
