Re: Re: crosstab for MS SQL

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@quasarz.com> wrote in message news:BCEDICKGNCBEMDHGNBCCEEFHDOAA.mailinglist@quasarz.com... > 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@mwct.de] > > Sent: Saturday, 28 December 2002 5:34 AM > > To: php-db@lists.php.net > > 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@thaithanakit.co.th> schrieb im Newsbeitrag > > > > news:5.1.0.14.2.20010924180544.022b8d80@langsuan.thaithanakit.co.th... > > > 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 > > > > >