How to using SQL 2000 create pivot view?

How to using SQL 2000 create pivot view?

Post by RENWE » Sat, 02 Feb 2002 12:27:20



How to using SQL 2000 create pivot view  as Access Create pivot(dynamic
diaplay fields).

Access example:
table
 a01    a02      a03
b001   a          100
b001   b          200
b001   c          100
b001   d          200
b001   e          300
            ....

view

a01     sum(a03)  a       b        c         d         e ...x
b001   900          100   200   100    200    300  ...x

Thanks.

 
 
 

How to using SQL 2000 create pivot view?

Post by Greg Obleshchu » Sat, 02 Feb 2002 13:02:40


HI RENWEI,
This is a copy from a post a did awhile ago.
     There are two or three approaches to this.  The first is where you know
 the number of crosstab columns i.e the months of a year or Quarters or
 something the important issue is that the crosstab column doesn't change.
 What you do here is write a select query with distinct or grouping on the
 row headings you want.  Then for each of the crosstab columns you do a
 subquery summarising the data. So for a crosstab that does a sales by month
 for regions you would do

 select distinct
     regions.,
     (select sum(salesvalue) from SalesTable ST2 where ST2.regions =
 ST1.regions and Month = 1) as SalesforJan ,
     (select sum(salesvalue) from SalesTable ST2 where ST2.regions =
 ST1.regions and Month = 2) as SalesforFeb ,
     (select sum(salesvalue) from SalesTable ST2 where ST2.regions =
 ST1.regions and Month = 3) as SalesforMar ,
     (select sum(salesvalue) from SalesTable ST2 where ST2.regions =
 ST1.regions and Month = 4) as SalesforApr ,
 ,...
     (select sum(salesvalue) from SalesTable ST2 where ST2.regions =
 ST1.regions and Month = 12) as SalesforDec ,
 from SalesTable ST1

 you get the picture.  The problem here is that if the data changes then
 maybe the query will miss sales , i.e if someone does something stupid like
 puts sales under month 13.  If this happens you would never know data is
 missing.  There would be database rules to stop this , but it does happen.

 The next example is a bit more complex.  This is where the crosstab columns
 vary, ie. the crosstab column is part of the data , you want to produce a
 crosstab of sales by Product for regions.

 What you have to do here is use a cursor based solution.  The basic method
 is to create a cursor of all the products in the data. I won't use syntax
 here I will use psuedo-code

 Start
     Select distinct product from SalesTable into a cursor called C1

     build a dynamic SQLstatement by looping through the cursor
     begin loop until no more records
         // Get the example here using the cursor build a big sql statement
         TempSQLStatement =     TempSQLStatement  & "  , (select
 sum(salesvalue) from SalesTable ST2 where ST1.regions ST1.regions and
 ST2.ProductCode = "         & C1.ProductCode & ") as [" & C1.ProductCode &
 "]"

         move to the next records
     end loop
     close cursor
     exec TempSQLStatement
 End

 As you can see from above the SQL statement can get very big very quickly.
 You need to worry about varchar character limits, and you need to worry
 about speed.  This approach works well for crosstabs with crosstab headings
 in the range of 1 to 20 or 30 .  More columns than that and the query
 becomes very very slow.  You can see this if you have every tried crosstab
 column on dates or products with over 200 or 300 products.  To get over the
 varchar character limit you can create a temp table and updates the temp
 table with the results from the summary subqueries.  Again though this is
 slow.

 The last approach is to use a third party tool,  there are a few on the
 market both the ones I am thinking of are of exception quality :)  The
third
 is pretty good too.
 If you are just after crosstab queries that are very quick and support
 upwards of 8000 columns, you can't go pasted A & G Software crosstab
 extended stored procedure.  This is my product so I think its pretty good.
 http://www.ag-software.com/AGS/xp_ags_crosstab.asp for more details.  Here
 is an example of the calling of xp_ags_crosstab to do the sales by product
 in regions.

 master.dbo.xp_ags_crosstab "Select * from Salesdb.dbo.SalesTable" ,
 "[Regions]" , "ProductCode]" ,  "[SalesValue]", 1

 very straight forward.
 If you want to do extra things to your crosstab like rename columns or
 replace data then RAC is the tool for you.  It allows you to do so much
more
 than crosstab reporting.  As a result the calling is more complex but this
 is more than out weighted by the options and results you get .  Look here
 for more details
 http://rac4sql.home.attbi.com

 The last option I know is to use a linked server to a MS Access database.
 What you do here is set-up a linked server through SQL Server 2000 to a jet
 db,   This jet db has linked tables to your sales database.  Then all you
 need to do is run a transform query to the linked server and return the
 results.  This can be a bit tricky to get running and there is the overhead
 of installing and running MS Access or JET on the SQL Server, which I know
a
 lot of DBA's will not do.

 SO in a nut shell there you are, big nutshell though.

 --
 I hope this helps
 regards
 Greg O MCSD
 No crosstab query in SQL Server
 try http://www.ag-software.com/ags/xp_ags_crosstab.asp


Quote:> How to using SQL 2000 create pivot view  as Access Create pivot(dynamic
> diaplay fields).

> Access example:
> table
>  a01    a02      a03
> b001   a          100
> b001   b          200
> b001   c          100
> b001   d          200
> b001   e          300
>             ....

> view

> a01     sum(a03)  a       b        c         d         e ...x
> b001   900          100   200   100    200    300  ...x

> Thanks.


 
 
 

How to using SQL 2000 create pivot view?

Post by Umachandar Jayachandra » Sat, 02 Feb 2002 13:24:11


    If you can fix the values of a02, then you can do:

select t.a01,
       sum( t.a03 ) as sum_a03,
       sum( case t.a02 when 'a' then t.a03 end ) as a,
       sum( case t.a02 when 'b' then t.a03 end ) as b,
       ...
       sum( case t.a02 when 'x' then t.a03 end ) as x
  from tbl as t
 group by t.a01

    Else you have to generate the fixed mapping IDs & use that instead or
use dynamic SQL.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

1. How to do PIVOT TABLE in SQL SERVER Using CREATE VIEWS COMMAND

Hi...  I'm Edy
I have problem with the Create View syntax for the PIVOT TABLE

I have data with the field :

Region    PERIOD       NAME  PRODUCT  UNIT
West      JAN-1999      A            AIR               10
East       FEB-1999      B            WATER       20
WEST    FEB-1999     A            AIR                30

How can i modify the table with the following like that :

REGION  Name  JAN   FEB  MAR MAY JUN JUL AUG   .......................
 WEST    A           10     30
  EAST    B           20

Can someone help me Please , i tried to do it in Access it's working but
in SQL - SERVER i don't know how  to make the PIVOT

best regards,
Edy Susanto

2. Site Down - backups corrupt

3. Creating a local view using CREATE SQL VIEW

4. SQL Command Editor

5. Using ACCESS 2000 to create SQL Server view and Store Procedures

6. Stored Procedure Problems!

7. Can not create view with access 2000 ADP to SQL 2000

8. TNS_ADMIN and SID

9. SQL 2000 B2: Can't create views/diagrams from Access 2000 project

10. Cannot view queries created using ADOX in Access 2000 interface

11. Views Created Using ADOX Are Not Visible in Access 2000

12. ODBC Error - saving a view in SQL 2000 using table from linked SQL 7

13. Creating Pivot Views