Best way to optimize SQL for table rotation in Sybase Ase

Question

I want to optimize the following SQL statement, which creates a set of rotary results from a historical table. This may be the most effective way to do this, but I keep thinking that there must be an easier way to create it.

SQL expression I'm trying to optimize

select Col1, Col2,
Max(case when TypeId = 1 then ColValue end) as Pivot1,
Max(case when TypeId = 2 then ColValue end) as Pivot2,
Max(case when TypeId = 3 then ColValue end) as Pivot3,
Max(case when TypeId = 4 then ColValue end) as Pivot4,
Max(case when TypeId = 5 then ColValue end) as Pivot5,
Max(case when TypeId = 6 then ColValue end) as Pivot6,
Max(case when TypeId = 7 then ColValue end) as Pivot7,
Max(case when TypeId = 8 then ColValue end) as Pivot8,
Max(case when TypeId = 9 then ColValue end) as Pivot9,
Max(case when TypeId = 10 then ColValue end) as Pivot10,
Max(case when TypeId = 11 then ColValue end) as Pivot11
from RowTable
group by Col1, Col2

UPDATE: Below is the table definition

CREATE TABLE dbo.RowTable  ( 
    Id                  int NOT NULL,
    Col1                char(8) NOT NULL,
    Col2                tinyint NOT NULL,
    TypeId              int NOT NULL,
    ColValue            datetime NOT NULL,
    CreatedBy           varchar(50) NOT NULL,
    Rowstamp            timestamp NOT NULL 
    )
LOCK DATAROWS
GO
ALTER TABLE dbo.RowTable
    ADD CONSTRAINT ukRowTable
    UNIQUE (Col1, Col2, TypeId)
    WITH max_rows_per_page = 0, reservepagegap = 0

Answer 1

Answer to the original question

1. Performance with the table as is.
Well, before anyone can evaluate this code, given performance, we need a create table statement, including indexes.

• .
  - , , . (), 3NF 5NF, , . . ( ), 6NF. , SQL .

  ( , , Dimension-Fact), , 6NF, , , , 5NF, Pivoted . DDL, , (true 6NF, , 5NF, 6NF). , , .

  "", 6NF.

• Pivot ( ), MAX() ( Pivotx ); , . , , . , , ( , , ).

  GROUP BY , ( , , ), . ? DDL , , .

, . -, 19 . -, SQL : ColValue 11 ; MAX(). , MAX() , GROUP BY. , ( , ). , .

, , (, , (Col1, Col2) is Unique? , , , . , , , , .

, , . , , (Col1, Col2, TypeId) ; Col1, Col2 TypeId ( ):

[Superceded, refer below]

, , .

, . . , , Scala. ; Pivot ( ). . , , Pivot. , ( DDL ).

, Pivot (ala MS SQL PIVOT) - . Pivot Unnormalised ; , Pivot 5NF; Pivot 6NF. .

• , . DDL, ParentTable, (Col1, Col2) .

• :

  SELECT  Col1, 
      Col2,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 1 ) as Latest_1,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 2 ) as Latest_2,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 3 ) as Latest_3,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 4 ) as Latest_4,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 5 ) as Latest_5,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 6 ) as Latest_6,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 7 ) as Latest_7,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 8 ) as Latest_8,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 9 ) as Latest_9,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId=10 ) as Latest_10,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId=11 ) as Latest_11
  FROM ParentTable  OUTER

• (.. ), " " SELECT-INTO :

  SELECT  Col1, 
      Col2,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 1 ) as Latest_1,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 2 ) as Latest_2,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 3 ) as Latest_3,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 4 ) as Latest_4,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 5 ) as Latest_5,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 6 ) as Latest_6,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 7 ) as Latest_7,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 8 ) as Latest_8,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId= 9 ) as Latest_9,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId=10 ) as Latest_10,
      ( SELECT ColValue FROM RowTable WHERE Col1=OUTER.Col1 AND Col2=OUTER.Col AND TypeId=11 ) as Latest_11
  FROM (
      SELECT DISTINCT
              Col1,
              Col2
          FROM RowTable
      )  OUTER

• Id RowTable, 100% , .

Answer 2

- . , .