php-db | 2002122

Re: [PHP-DB] Re: crosstab for MS SQL From: news.php.net (heyjohnlim <email protected>)
Date: 12/28/02

Hi

ADOdb has a crosstab function that generates the sql for you. This is a
utility function, so you do not need to use the rest of ADOdb if you don't
want to.

Download: http://php.weblogs.com/adodb

Docs: http://phplens.com/lens/adodb/readme.htm#pivot

"Opec Kemp" <mailinglist <email protected>> wrote in message
news:BCEDICKGNCBEMDHGNBCCEEFHDOAA.mailinglist <email protected>
> Hi,
>
> I don't think you can do that with MS SQL, it doesn't support TRASFORN, I
> think that's strictly Access syntax. MS SQL 7 and up does have ROLLUP etc
> but that's part of the OLAP service, which you will only get if you have
> Enterprise edition (read $$$$$$ :)).
>
> The easiest way to get out of this is to use the CASE statement. For
> example:
>
> 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 used 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 Pivot
> GROUP BY Year
> GO
>
> This will give you the result:
>
> +-------+-----+------+------+-----+
> | Year | Q1 | Q2 | Q3 | Q4 |
> +-------+-----+------+------+-----+
> | 1990 | 1.1 | 1.2 | 1.3 | 1.4 |
> +-------+-----+------+------+-----+
> | 1991 | 2.1 | 2.2 | 2.3 | 2.4 |
> +-------+-----+------+------+-----+
>
> This works with MS SQL 7 and 2000. I don't know if 6.5 supports this
though
> :(
>
>
> > -----Original Message-----
> > From: MWCT - Markus Weber [mailto:mweber <email protected>]
> > Sent: Saturday, 28 December 2002 5:34 AM
> > To: php-db <email protected>
> > Subject: [PHP-DB] Re: crosstab for MS SQL
> >
> >
> > Have you tried the MS Access Syntax ? - e.g.:
> >
> > TRANSFORM Sum(cube_by_c.netc_bd) AS [Summe von netc_bd]
> > SELECT cube_by_c.pthr_product_c AS PROD_C, xref_prod.pthr_desc_x AS
[DESC]
> > FROM cube_by_c LEFT JOIN xref_prod ON cube_by_c.pthr_product_c =
> > xref_prod.pthr_product_c
> > WHERE (((cube_by_c.country_iso3_c)="DEU"))
> > GROUP BY cube_by_c.pthr_product_c, xref_prod.pthr_desc_x
> > PIVOT cube_by_c.yyyymm;
> >
> > regards,
> > markus
> >
> >
> >
> > "Sommai Fongnamthip" <sommai <email protected>> schrieb im Newsbeitrag
> > news:5.1.0.14.2.20010924180544.022b8d80 <email protected>
> > > Hi,
> > > MySQL has a method to write Crosstab method but It can't use with MS
> > > SQL. Did someone know how to write SQL statement for make
> > cross tab with
> > > MS SQL 6.5 (both ODBC and TDS connection)?
> > >
> > > Thank you
> > > SF
> > >
> >
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php