Re: Transpose in SQL

From: Brad K <koehlerb_at_NO.SPAM.w-link.net>
Date: 1998/10/25
Message-ID: <70v8pt$s0k$1@supernews.com>#1/1

It is possible in Oracle by using the proprietary DECODE statement. I think this can be done in SQLServer using the CASE statement. The Decode statement looks like this.

SELECT
  (DECODE(TagName,'Fermentation volume',Tag_Value,NULL) "Fermentation volume",
  (DECODE(TagName,'Fermentation pH',Tag_Value,NULL) "Fermentation pH", FROM
  Fermentation

in SQL Server this I guess this would be

SELECT
  TagName "Fermentation volume" =

        CASE
            WHEN TagName IS NULL THEN NULL
            WHEN TagName IS 'Fermentation volume' THEN Tag_Value
        END,
  TagName "Fermentation pH" =
        CASE
            WHEN TagName IS NULL THEN NULL
            WHEN TagName IS 'Fermentation pH'THEN Tag_Value
        END

FROM
  Fermentation

NULL Values can be stripped out using aggregate functions, and by nesting the SQL statement as:

SELECT Fermentation volume" ,"Fermentation pH" FROM  (
 SELECT DISTINCT
  AVG(DECODE(TagName,'Fermentation volume',Tag_Value,NULL)) "Fermentation volume",
  AVG(DECODE(TagName,'Fermentation pH',Tag_Value,NULL)) "Fermentation pH", FROM
  Fermentation
)
WHERE
  "Fermentation volume" IS NOT NULL OR
  "Fermentation pH" IS NOT NULL ;

Reid Lai wrote in message <3630A33A.DD42FDFB_at_hk.super.net>...
>Not possible only with SQL. You may utilize PL/SQL as well.
>
>Ng K C Paul wrote:
>
>> Is it possible to do the Transpose function using SQL like under Excel's
>> Paste Special - change columns of data to rows, and vice versa?
>
>
>
>--
>Best Regards,
>
>Reid Lai <reidlai_at_hk.super.net>
>Certified Oracle7 DBA (OCP)
>================================================
>The above opinions are mine and do not represent
>any official standpoints of my employer
>
>
Received on Sun Oct 25 1998 - 00:00:00 CDT