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:[EMAIL PROTECTED]]
> Sent: Saturday, 28 December 2002 5:34 AM
> To: [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" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
> [EMAIL PROTECTED]">news:[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

Reply via email to