Fake a crosstab query

Fake a crosstab query

Post by Matt Epstei » Fri, 14 Jun 2002 03:39:22



Anybody have any hints on how to "fake" a crosstab report in an ADP?
The report is for the total sales for each of our different sales reps for
each of the last 7 days, so the number of columns would never change -- I
have read in the newsgroup that helps.
My boss won't let me use 3rd party solutions, so I need to do this with the
tools I already have.

TIA,
Matt Epstein

 
 
 

Fake a crosstab query

Post by John Sa » Fri, 14 Jun 2002 17:45:33


Use this one, you ll love it!

('m sorry, I don't have the name of the author of this routine, If someone
does, please send me his name to include in the code)

CREATE PROC sp_CrossTab





AS
DECLARE



-- step 1: beginning of SQL string



  CASE

    ELSE ''
  END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)



  'INSERT INTO #keys ' +






BEGIN




           ''' THEN ' + CASE







END






GO

 
 
 

Fake a crosstab query

Post by Matt Epste » Sat, 15 Jun 2002 03:58:02


John:

I can't get this to work. Using Access 2000 and SQL Server 2000, I get
a "You cannot change the object type in a script." error.

I am new at sprocs and T-SQL so I am having trouble seeing the
problem.

Matt


> Use this one, you ll love it!

> ('m sorry, I don't have the name of the author of this routine, If someone
> does, please send me his name to include in the code)

> CREATE PROC sp_CrossTab





> AS
> DECLARE



> -- step 1: beginning of SQL string



>   CASE

>     ELSE ''
>   END

> CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)



>   'INSERT INTO #keys ' +






> BEGIN




>            ''' THEN ' + CASE







> END






> GO

 
 
 

Fake a crosstab query

Post by Jeerakarn Wataki » Tue, 18 Jun 2002 12:34:34


Look at this may be help. It's an on line help in SQLServer 7.0
Cross-tab Reports
Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating a PivotTable?, creating a cross-tab report, or rotating data.

Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:

Year        Quarter        Amount

----        -------        ------

1990        1             1.1

1990        2             1.2

1990        3             1.3

1990        4             1.4

1991        1             2.1

1991        2             2.2

1991        3             2.3

1991        4             2.4

A report must be produced with a table that contains one row for each year, with the values for each quarter appearing in a separate column, such as:

      Year Q1 Q2 Q3 Q4
      1990 1.1 1.2 1.3 1.4
      1991 2.1 2.2 2.3 2.4

These are the statements to create the Pivot table and populate it with the data from the first table:

USE Northwind

GO

CREATE TABLE Pivot

( Year        SMALLINT,

  Quarter    TINYINT,

  Amount        DECIMAL(2,1) )

GO

INSERT INTO Pivot VALUES (1990, 1, 1.1)

INSERT INTO Pivot VALUES (1990, 2, 1.2)

INSERT INTO Pivot VALUES (1990, 3, 1.3)

INSERT INTO Pivot VALUES (1990, 4, 1.4)

INSERT INTO Pivot VALUES (1991, 1, 2.1)

INSERT INTO Pivot VALUES (1991, 2, 2.2)

INSERT INTO Pivot VALUES (1991, 3, 2.3)

INSERT INTO Pivot VALUES (1991, 4, 2.4)

GO

This is the SELECT statement to create the rotated results:

SELECT Year,

    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,

    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,

    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,

    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4

FROM Northwind.dbo.Pivot

GROUP BY Year

GO

This SELECT statement also handles a table in which there are multiple rows for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper column in the result set and 0 is added to the result set columns for the other quarters.

If the results of this SELECT statement are used as input to a spreadsheet, it is easy for the spreadsheet to calculate a total for each year. When the SELECT is used from an application it may be easier to enhance the SELECT statement to calculate the yearly total, for example:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal

FROM (SELECT Year,

             SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,

             SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,

             SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,

             SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4

     FROM Pivot AS P

     GROUP BY P.Year) AS P1

GO

You may also want to consider GROUP BY with CUBE or with ROLLUP. Both compute the same sort of information, but in a slightly different format.


> Anybody have any hints on how to "fake" a crosstab report in an ADP?
> The report is for the total sales for each of our different sales reps for
> each of the last 7 days, so the number of columns would never change -- I
> have read in the newsgroup that helps.
> My boss won't let me use 3rd party solutions, so I need to do this with the
> tools I already have.

> TIA,
> Matt Epstein

 
 
 

1. Content box in page with fake fixed background (watermarkish), fake textured scrollbar, scalable (including font-size)

Great effect, thanks for the tut! We'll try it soon.
Cheers, Ed


Mid page scalable text box with fixed backgroundimage and textured
scrollbar.
Use is made of JScript expressions on positioning elements. (IE55 users
might get errors). The DIV 'bodydummycontainer' acts as dummy for the
textbox canvas. Set the Alpha filter opacity between 40 and 70% depending on
how good you will have the image shine trough and the scrollbar colors
expressesed. Do not place content outside the 'bodydummycontainer' div (that
is in the 'boxcanvas' div) or the effect will be lost.
The DIV 'contentcontainer' is the container in which all message content
should be placed. For the 'boxcanvas' background image use an image with a
width of 21px which is suitabel for 1024px resolution screens. (actually
800px resoltion screen use a 17px scrollbar so we could use a conditional
statement to show different stylesheets (you have to make a 17px image and
place that in the alternative stylesheet).
A dummy bkg image is included in the contentcontainer div whereby its
position is fixed using some seamingly inticate usage of
offsetHeight/scrollTop and image offsetHeight. The effect is like a
watermark.(IE55 on Macintosh only supports background:fixed).
Font size depends on boxcanvas width (try resizing the window!)
Cybarber, December 15th, 2002

2. Random Behaviour

3. Creating a Crosstab query based on a different crosstab query where the fields can change.

4. juno

5. BUY FAKE NIGERIA PASSPORT,FAKE PASSPORT OF SINGAPORE FOR SELL,FALSE USA PASSPORTS

6. linked graphs

7. Crosstab Problem: Jet doesn't like criteria in crosstab query

8. Unique table

9. Crosstab query: How to return a zero when crosstab field is null?

10. Faking multiple headers in a query

11. Need to Fake a AutoNumber field in a Query

12. Using Filter condition on Select Query within a Crosstab Query in Code