Please Help me Conversion CrossTab Access to SQL Server 2000 Query

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

Please Help me

Thank You

Hi Sam,

Today, my recommendation is the same one that I gave you yesterday. Click here to check it out. Several folks have taken their time and given you suggestions -and you don't seem to be learning from those suggestions.

In fact, I'll post it again:

I suggest that you take the time to work through the suggestions that have been provided to your previous posts until you understand the process.

When you work through the process and begin to understand the process, you will be able to work these out for yourself. (Otherwise, we might think that you are just trying to get folks to do your work for you. And that probably wouldn't be fair to you ...)

|||not understand the process Function TRANSFORM and PIVOT?please help me

thank you

|||

Transform is NOT a Transact-SQL keyword -in this situation, you can probably safely ignore it.

PIVOT is a SQL 2005 keyword that displays rows as columns.

You 'should' have a copy of SQL Server 2005 Books Online. You can get a lot of good information from that source.

A couple of folks indicated to you in your other posts that IIF was not a SQL function (it is an Access function), and they offered suggestions about how to turn IIF functions into CASE structures. Perhaps looking in Books Online and reading about using CASE would be useful.

I, for one, am willing to assist those that seem intent on learning and helping themselves. BUT I get annoyed to repeated be asked to do someone's work for them -UNLESS, of course, I'm getting paid for it.

Just in case others here are not so compelled as I, I shall refrain from additional comments on your posts. Good luck.

|||

This query will help you...

Code Snippet

Declare @.JoinQuery as Varchar(1000);
Declare @.SelectQuery as Varchar(1000);
Declare @.PreparedJoinQuery as Varchar(1000);
Declare @.PreparedSelectQuery as Varchar(1000);
Select @.JoinQuery = '', @.SelectQuery = ''
Select @.PreparedJoinQuery = 'Left Outer Join OILPALM as [?] On [?].Year=? and [?].NAME=[Main].NAME '
Select @.PreparedSelectQuery =',Count([?].BLOCK) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From OILPALM Group By Year

Exec ('Select [Main].NAME' + @.SelectQuery + ' From (Select Distinct NAME From OILPALM) as [Main]' + @.JoinQuery + ' Group By [Main].NAME')

|||This query help me....Thank you so much...

Please help - problem with simple SQL - SUM ?

Hello All, after writing my simple SQL i don't know how to fix this problem, SUM problem, won't run because of it.

I'm so stressed out, please help, need to find the problem

this is the code:

1. REPORT CARD

CREATE VIEW REPORT_CARD AS
SELECT DEPT_NAME, COURSE_NUM, COURSE_DESC, GRADE_L, COURSE_CREDIT as
CREDITS_TAKEN, COURSE_CREDIT AS COURSE_EARNED, GRADE_N*COURSE_CREDIT AS
GRADE_POINTS,
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
CLASS.SEM_ID,
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE,
S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP
FROM STUDENT, DEPARTMENT, COURSE, ENROLLMENT, CLASS, SEMESTER
WHERE
STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND SEMESTER.SEM_ID=CLASS.SEM_ID;

2. CLASS LIST

CREATE VIEW CLASS_LIST AS
SELECT COURSE.DEPT_CODE "DEPARTMENT", COURSE_NUM "COURSE", SEC_NUM
"SECTION', CLASS.SCHED_CODE, DEPT_NAME, COURSE_DESC, COURSE_CREDIT,
SEM_ID,
TIME, DAY, ROOM_NUM, BLDG_CODE,
CLASS.F_ID, F_FNAME, F_LNAME
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, GRADE
FROM DEPARTMENT, CLASS, COURSE, ROOM, STUDENT, ENROLLMENT, FACULTY
WHERE
ROOM.ROOM_ID=CLASS.ROOM_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND ENROLLMENT S_ID=STUDENT.S_ID;

3.GRADE VERIFICATION REPORT

CREATE VIEW GRADE_VERIFICATION AS
SELECT * FROM CLASS_LIST;

4.TIME SCHEDULE

CREATE VIEW TIME_SCHEDULE AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT, PREREQ
FROM CLASS, COURSE, FACULTY, ROOM
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID;

6.STUDENT SCHEDULE

CREATE VIEW STUDENT_SCHEDULE AS
SELECT STUDENT.S_ID, SEM_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY,
S_L_STATE, S_L_ZIP, S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
CLASS.SCHED_CODE, CLASS_CLASS_ID, COURSE_DESC, SEC_NUM,
COURSE_CREDIT, TIME, DAY, ROOM_NUM, BLDG_CODE, SUM(COURSE_CREDIT)
FROM STUDENT, CLASS, COURSE, ROOM, ENROLLMENT
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID;

7.FULL STUDENT INFORMATION

CREATE VIEW FULL_STUDENT_INFO AS
SELECT STUDENT.*, SEM_ID,
COURSE_NUM, COURSE_DES, COURSE_CREDIT,
GRADE_L, GRADE_N*COURSE_CREDIT AS "GRADE POINTS",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(GRADE_N*COURSE_CREDIT) AS "TOTAL GRADE POINTS",
S_GPA
FROM STUDENT, ENROLLMENT, COURSE, CLASS
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_ID=CLASS.SCHED_ID
AND CLASS.CLASS_ID=COURSE_COURSE_ID;

8. FACULTY INFORMATION REPORT

CREATE VIEW FACULTY_INFO AS
SELECT FACULTY.*,
S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE, S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
DEPT_CODE, MAJOR_NAME
FROM FACULTY, STUDENT, MAJOR
WHERE
FACULTY.F_ID=STUDENT.F_ID
AND STUDENT.MAJOR_ID=MAJOR.MAJOR_ID;

9.WORK VERSION OF THE TIME SCHEDULE

CREATE VIEW TIME_SCHED_INTERNAL AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT,
PREREQ,
COUNT(ENROLL_ID), MAX_ENROLL
FROM CLASS, COURSE, FACULTY, ROOM, ENROLLMENT
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE;

10.CREATE VIEW COURSE_REPORT AS
SELECT COURSE_NUM, COURSE_DESC, COURSE_CREDIT,
DEPT_DESC, COURSE.DEPT_CODE
FROM COURSE, DEPARTMENT
WHERE COURSE.DEPT_CODE=DEPARTMENT.DEPT.CODE;

11.UPDATE ENROLLMENT-NEED TO CREATE A TRIGGER TO CHECK UPON INSERT INTO
THE ENROLLMENT TABLE WHERE COURSE.PREREQ NOT NULL AND ENROLLMENT.GRADE NOT
NULL FOR THAT STUDENT

SYNTAX WILL BE SIMILAR TO:
CREATE OR REPLACE TRIGGER VERIFY_ENROLLMENT
BEFORE INSERT ON ENROLLMENT
BEGIN
******
END

12.POST GRADES: EXAMPLE

UPDATE ENROLLMENT
SET GRADE='A'
WHERE S_ID='123';

13. PURGE:EXAMPLE
DELETE FROM CLASS
WHERE CLASS < 1010;can u post the ddl for the base tables ?
and which view is causing the problem?

Originally posted by Mari9922
Hello All, after writing my simple SQL i don't know how to fix this problem, SUM problem, won't run because of it.

I'm so stressed out, please help, need to find the problem

this is the code:

1. REPORT CARD

CREATE VIEW REPORT_CARD AS
SELECT DEPT_NAME, COURSE_NUM, COURSE_DESC, GRADE_L, COURSE_CREDIT as
CREDITS_TAKEN, COURSE_CREDIT AS COURSE_EARNED, GRADE_N*COURSE_CREDIT AS
GRADE_POINTS,
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
CLASS.SEM_ID,
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE,
S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP
FROM STUDENT, DEPARTMENT, COURSE, ENROLLMENT, CLASS, SEMESTER
WHERE
STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND SEMESTER.SEM_ID=CLASS.SEM_ID;

2. CLASS LIST

CREATE VIEW CLASS_LIST AS
SELECT COURSE.DEPT_CODE "DEPARTMENT", COURSE_NUM "COURSE", SEC_NUM
"SECTION', CLASS.SCHED_CODE, DEPT_NAME, COURSE_DESC, COURSE_CREDIT,
SEM_ID,
TIME, DAY, ROOM_NUM, BLDG_CODE,
CLASS.F_ID, F_FNAME, F_LNAME
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, GRADE
FROM DEPARTMENT, CLASS, COURSE, ROOM, STUDENT, ENROLLMENT, FACULTY
WHERE
ROOM.ROOM_ID=CLASS.ROOM_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND ENROLLMENT S_ID=STUDENT.S_ID;

3.GRADE VERIFICATION REPORT

CREATE VIEW GRADE_VERIFICATION AS
SELECT * FROM CLASS_LIST;

4.TIME SCHEDULE

CREATE VIEW TIME_SCHEDULE AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT, PREREQ
FROM CLASS, COURSE, FACULTY, ROOM
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID;

6.STUDENT SCHEDULE

CREATE VIEW STUDENT_SCHEDULE AS
SELECT STUDENT.S_ID, SEM_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY,
S_L_STATE, S_L_ZIP, S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
CLASS.SCHED_CODE, CLASS_CLASS_ID, COURSE_DESC, SEC_NUM,
COURSE_CREDIT, TIME, DAY, ROOM_NUM, BLDG_CODE, SUM(COURSE_CREDIT)
FROM STUDENT, CLASS, COURSE, ROOM, ENROLLMENT
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID;

7.FULL STUDENT INFORMATION

CREATE VIEW FULL_STUDENT_INFO AS
SELECT STUDENT.*, SEM_ID,
COURSE_NUM, COURSE_DES, COURSE_CREDIT,
GRADE_L, GRADE_N*COURSE_CREDIT AS "GRADE POINTS",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(GRADE_N*COURSE_CREDIT) AS "TOTAL GRADE POINTS",
S_GPA
FROM STUDENT, ENROLLMENT, COURSE, CLASS
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_ID=CLASS.SCHED_ID
AND CLASS.CLASS_ID=COURSE_COURSE_ID;

8. FACULTY INFORMATION REPORT

CREATE VIEW FACULTY_INFO AS
SELECT FACULTY.*,
S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE, S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
DEPT_CODE, MAJOR_NAME
FROM FACULTY, STUDENT, MAJOR
WHERE
FACULTY.F_ID=STUDENT.F_ID
AND STUDENT.MAJOR_ID=MAJOR.MAJOR_ID;

9.WORK VERSION OF THE TIME SCHEDULE

CREATE VIEW TIME_SCHED_INTERNAL AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT,
PREREQ,
COUNT(ENROLL_ID), MAX_ENROLL
FROM CLASS, COURSE, FACULTY, ROOM, ENROLLMENT
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE;

10.CREATE VIEW COURSE_REPORT AS
SELECT COURSE_NUM, COURSE_DESC, COURSE_CREDIT,
DEPT_DESC, COURSE.DEPT_CODE
FROM COURSE, DEPARTMENT
WHERE COURSE.DEPT_CODE=DEPARTMENT.DEPT.CODE;

11.UPDATE ENROLLMENT-NEED TO CREATE A TRIGGER TO CHECK UPON INSERT INTO
THE ENROLLMENT TABLE WHERE COURSE.PREREQ NOT NULL AND ENROLLMENT.GRADE NOT
NULL FOR THAT STUDENT

SYNTAX WILL BE SIMILAR TO:
CREATE OR REPLACE TRIGGER VERIFY_ENROLLMENT
BEFORE INSERT ON ENROLLMENT
BEGIN
******
END

12.POST GRADES: EXAMPLE

UPDATE ENROLLMENT
SET GRADE='A'
WHERE S_ID='123';

13. PURGE:EXAMPLE
DELETE FROM CLASS
WHERE CLASS < 1010;|||Originally posted by harshal_in
can u post the ddl for the base tables ?
and which view is causing the problem?|||Thank you very much, I'll post it after work, if that's not too much of a problem.

Originally posted by harshal_in
can u post the ddl for the base tables ?
and which view is causing the problem?

Placing a sum in a table header column

I have been requested to add the sum of an interger field to the table header. I have the sum in the footer (which is very easy to do), but I cannot get the sum to appear in the table header.

I then set-up the stored procedure to run the sum, and place it into a dummy field. I still cannot add this field to the table header. Instead of printing the data for the dummy field (the correct total), it instead prints the actual field name on the report.

Is there anyway to place a sum in a table header on a SQL Server Report?

Let me know.

Thank you,

T.J.

You should be able to just copy the code that you're using in the sum field in your table footer into a textbox in the table header.

Jarret

|||

Thank you very much. I got busy and didn't get time to get back to this yesterday.

I tested this today, and if I just drop the field into a blank header cell, the number displays correctly.

But when I place this field in with text (such as "# of Boxes" and then add in the summed field, it displays the text of the field name, rather than the value (the number).

Very strange. When mixed with text, it displays the field name rather than the actual value.

Is there anyway to work around this?

Thank you,

T.J.

|||

Can you post your expression you have in your textbox?

You should be able to use something like this:

="# of Boxes - " & count(Fields!boxes.Value)

Jarret

|||

Yes, and thank you...

"# of Boxes - " & Sum(Fields_CountBoxes.Value)

When I preview this, it displays exactly like my formula.

Where if I just drop Sum(Fields_CountBoxes.Value) into the column header, it gives just the summed number I want to display.

|||

You are missing the '='. Try putting this into your textbox:

="# of Boxes - " & Sum(Fields_CountBoxes.Value)

If you don't have the '=' at the front, it takes whatever you have in there as literal. The '=' makes it an expression.

Jarret

|||

Oh, what an over sight.

Thank you very much, that fixed my error!

|||

Glad I could help TJ. Can you mark this one as answered so others can see this solution?

Thanks.

Jarret

PL/SQL effeciency question

Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

Any suggestion is appreciated

MarkOriginally posted by mchih
Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

Any suggestion is appreciated

Mark
Generally it is preferable to avoid procedural logic if you want best performance. Maybe you could combine all (or many of) your counts and sums into a single query using DECODE (or CASE) to filter the records:

SELECT SUM( DECODE( col1, 'x', 1, 0 )) as COUNT_WHERE_COL1_IS X
, COUNT(*) TOTAL_COUNT,
, SUM( DECODE( col3, 123, col4, 0 )) as SUM_COL4_WHERE_COL3_IS_123
...
FROM ...|||it might be difficult to put many of the query together, since each of them has a different where clause.

eg:

table temp(
card_type
card_amount
message_type
message_response
.
.
.
)

a sample query would be
select count(*), sum(card_amount) from temp
where card_type = x
and message_type = y
and message_response = z;

now that i think about it, it might be possible to use a GROUP BY to get all the values with similar WHERE clause, but I don't know how to store the values individually so i can access them later (eg. put it into different table)

TIA

Mark

pivot t-sql adding a percentage column

in a cross table result is very common to add a percentage column in respect

of the sum of another column

for example

x z%

a 2 0,46 = 2/(2+5+6)

b 5 0,38 = 5/(2+5+6)

c 6 0,15 = 6/(2+5+6)

is there an easy way to do that in t-sql using the pivot command ?

This was going to be my first answer

"Not easily that I can think of because it requires knowledge of the other rows in the result set and that is where SQL is not very good. That is performing calculations at two levels one aggregate is across th

You need to find the total and then calculate the percentage. This is normally done using a derived table"

However in digging I looked into windowing. This is a new feature that can be used with the new ranking functions as well as the normal aggregates.

In your situation you can do

select x, 1.0*x / sum(x) over (parition by 1) percentage

from mytable

What this does is that it performs a sum of x over the partition of 1 (as this is the same for each row it does a sum across the whole resultset).

Pivot Question

I want to pivot some data returned from a query. I've use the standard
technique [Sum(Case...)] below in the past, but in this situation it's not
ideal.
Typical solution:
SELECT
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2002
In my case, I'm listing the Parts that need despatching and how many are
needed of each. The first issue is that there are 30-50 parts typically, and
I don't think that 30-50 Sum(Case...) statements are the best solution.
Secondly (and more importantly), new parts are added and removed on a
regular basis, so hard-coding like this is pure folly.
What are the best alternatives to this? Are there any?
I have a way around this problem using my ASP code, but I'm interested in a
general solution for such a scenario for future purposes as well.
Thanks
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Am I missing something.
Grouping on part name or number seems the obvious solution.|||Am I missing something.
Grouping on part name or number seems the obvious solution.|||"andy" <aon14@.lycos.co.uk> wrote in message
news:1134388670.211751.172940@.o13g2000cwo.googlegroups.com...
> Am I missing something.
Either of us could be...

> Grouping on part name or number seems the obvious solution.
>
Yeah that gets me so far...
PartNo - Count:
Part1 - 10
Part2 - 20
Part3 - 15
Whereas I want:
Part1 - Part2 - Part3
10 - 20 - 15
This is a bit of a contrived example since the solution is easy enough to
achieve in my ASP code, but I've had examples in the past where the ASP
solution wasnt appropriate...
CJM|||The best solution is the one you've already cited; do it on the client
side. Your server will love you for it :)
However, if you must do it on the server side, google for "SQL Server
dynamic pivot" for various solutions to this problem. Most have
limitations, but they can do the trick. As an aside, SQL Server 2005
has a PIVOT command built-in.|||Hi,
Why not try a third party product
AGS Crosstab Builder for SQL 2000
www.ag-software.com
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OR6%23VEx$FHA.2784@.tk2msftngp13.phx.gbl...
>I want to pivot some data returned from a query. I've use the standard
>technique [Sum(Case...)] below in the past, but in this situation it's not
>ideal.
> Typical solution:
> SELECT
> SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
> ,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
> ,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
> ,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
> ,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
> ,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
> ,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
> ,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
> ,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
> ,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
> ,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
> ,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
> FROM Sales.SalesOrderHeader
> WHERE YEAR(OrderDate) = 2002
> In my case, I'm listing the Parts that need despatching and how many are
> needed of each. The first issue is that there are 30-50 parts typically,
> and I don't think that 30-50 Sum(Case...) statements are the best
> solution. Secondly (and more importantly), new parts are added and removed
> on a regular basis, so hard-coding like this is pure folly.
> What are the best alternatives to this? Are there any?
> I have a way around this problem using my ASP code, but I'm interested in
> a general solution for such a scenario for future purposes as well.
> Thanks
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||There is no way to dynamically create a pivot (even in 2k5 iirc)
Unless you goto the extremes and create dynamic sql statements and use
EXEC( ... ) - which is generally very hard to maintain and has performance
impacts.
Your best bet is to return the data to the client as a two field dataset and
cross tab it in the front end.
Part Number
-- --
KSTX 1345
QUZR 45
38XJ 8723
9MSU 1437
SM5J 127
MABV 731
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OR6%23VEx$FHA.2784@.tk2msftngp13.phx.gbl...
> I want to pivot some data returned from a query. I've use the standard
> technique [Sum(Case...)] below in the past, but in this situation it's not
> ideal.
> Typical solution:
> SELECT
> SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
> ,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
> ,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
> ,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
> ,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
> ,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
> ,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
> ,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
> ,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
> ,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
> ,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
> ,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
> FROM Sales.SalesOrderHeader
> WHERE YEAR(OrderDate) = 2002
> In my case, I'm listing the Parts that need despatching and how many are
> needed of each. The first issue is that there are 30-50 parts typically,
and
> I don't think that 30-50 Sum(Case...) statements are the best solution.
> Secondly (and more importantly), new parts are added and removed on a
> regular basis, so hard-coding like this is pure folly.
> What are the best alternatives to this? Are there any?
> I have a way around this problem using my ASP code, but I'm interested in
a
> general solution for such a scenario for future purposes as well.
> Thanks
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1134389360.359667.322330@.g14g2000cwa.googlegroups.com...
> The best solution is the one you've already cited; do it on the client
> side. Your server will love you for it :)
> However, if you must do it on the server side, google for "SQL Server
> dynamic pivot" for various solutions to this problem. Most have
> limitations, but they can do the trick. As an aside, SQL Server 2005
> has a PIVOT command built-in.
>
One thing hampering my search via google is the proliferation of help on SQL
2005!
Still... should be migrating across soon...|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1134389360.359667.322330@.g14g2000cwa.googlegroups.com...
> The best solution is the one you've already cited; do it on the client
> side. Your server will love you for it :)
> ... SQL Server 2005 has a PIVOT command built-in.
She loves me...she loves me not...:)
For dynamic xtabs and other type of pivoting problems on the server side
check out RAC @.
www.rac4sql.net

pivot query : access to msSql

Hi; i'm trying to get this pivot query working in msSql:

TRANSFORM Sum(tblAssortiment.Aantal) AS SumOfAantal

SELECT tblAssortiment.Assortiment

FROM tblAssortiment

WHERE (((tblAssortiment.Artikel)=?))

GROUP BY tblAssortiment.Assortiment

PIVOT tblAssortiment.Maat;

Any ideas?

Thank you!

It's difficult to determine what you are trying to do without the full schema of your database or more information, but you should start here to find out more about the PIVOT statement in SQL Server:

http://msdn2.microsoft.com/en-us/library/ms177410.aspx

Pivot Multiple Values

Is there a way to pivot multiplie values in one 'run'.... In the order of ...

PIVOT ( SUM(DSH_TICKETS) FOR CPRF_NBR IN ([1], [2], [3], [4], [5])

SUM(HALL_CAPACITY) FOR CPRF_NBR IN ([1], [2], [3], [4], [5]) ) PVT

I know that there would be a problem with the headers, but that i could solve by using a second dummy for cprf_nbr and increase it with 10 (ex.)

Until knwo i did the jobg with a case statement, but it would be much nicer with a PIVOT.

No. This is not possible with PIVOT operator. You will have to use the old approach of using multiple aggregate functions with CASE expressions and GROUP BY which is what PIVOT does right now.|||

I am not 100% sure that this matches, but I wrote a blog about taking a set that looked like:

GroupBy PropertyName value1 value2
- -- --
First Property1 1.00 2.00
First Property2 2.00 4.00
Second Property1 4.00 8.00
Second Property2 8.00 16.00

And pivoted to look like:

groupBy Property1-value1 Property1-value2 Property2-value1 Property2-value2
- - -- - -
First 1.00 2.00 2.00 4.00
Second 4.00 8.00 8.00 16.00

The idea was to add another layer in there and Break down the set into two queries (in your case one for DSH_TICKETS, and another for HALL_CAPACITY) and then change the names of the columns to DSH_TICKETS-1, DSH_TICKETS-2, etc, then pivot on these names for the group.

Here it is: http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!758.entry

There I used max, but sum should work (I think :)