6 Replies Latest reply on Jun 14, 2013 8:24 AM by Acey Boyce

    Rotate Data from Rows To Columns from Excel

    Acey Boyce

      I am importing data from an Excel document. It is in format of....

       

      elapsed_time     match_id     1_1     1_2     1_3     ...     5_4

       

      I need it in the format of

       

      elapsed_time      match_id     MAPLOC      Control_Weight

       

      Where MAPLOC is 1_1 through 5_4 and Control Weight is the value from the excel sheet.

       

      I have done a similar transformation using Custom Excel drawing data from a data base. It converts from

       

      Player1x     Player1y     Player2x     Player2y     ...     Match     Time Elapsed

       

      to

       

      Player     x     y     Match     Time Elapsed

       

      This was done with the following custom SQL:

       

      SELECT

          T.N as "player",

          CASE T.N

              WHEN 1 THEN "table"."player1_loc_x"

              WHEN 2 THEN "table"."player2_loc_x"

              WHEN 3 THEN "table"."player3_loc_x"

              WHEN 4 THEN "table"."player4_loc_x"

              WHEN 5 THEN "table"."player5_loc_x"

              WHEN 6 THEN "table."player6_loc_x"

              WHEN 7 THEN "table"."player7_loc_x"

              WHEN 8 THEN "table"."player8_loc_x"

              WHEN 9 THEN "table"."player9_loc_x"

              WHEN 10 THEN "table"."player10_loc_x"

          END as "loc_x",

          CASE T.N

              WHEN 1 THEN "table"."player1_loc_y"

              WHEN 2 THEN "table"."player2_loc_y"

              WHEN 3 THEN "table"."player3_loc_y"

              WHEN 4 THEN "table"."player4_loc_y"

              WHEN 5 THEN "table"."player5_loc_y"

              WHEN 6 THEN "table"."player6_loc_y"

              WHEN 7 THEN "table"."player7_loc_y"

              WHEN 8 THEN "table"."player8_loc_y"

              WHEN 9 THEN "table"."player9_loc_y"

              WHEN 10 THEN "table"."player10_loc_y"

          END as "loc_y",

          "table"."match_id",

          "table"."map_elapsed_time"

      FROM "public"."table"

          CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(N)

       

      I created the following custom SQL for the Excel data

       

      SELECT

          CASE T.N

              WHEN 1 THEN "1_1"

              WHEN 2 THEN "1_2"

              WHEN 3 THEN "1_3"

              WHEN 4 THEN "1_4"

              WHEN 5 THEN "2_1"

              WHEN 6 THEN "2_2"

              WHEN 7 THEN "2_3"

              WHEN 8 THEN "2_4"

              WHEN 9 THEN "3_1"

              WHEN 10 THEN "3_2"

              WHEN 11 THEN "3_3"

              WHEN 12 THEN "3_4:

              WHEN 13 THEN "4_1"

              WHEN 14 THEN "4_2"

              WHEN 15 THEN "4_3"

              WHEN 16 THEN "4_4"

              WHEN 17 THEN "5_1"

              WHEN 18 THEN "5_2"

              WHEN 19 THEN "5_3"

              WHEN 20 THEN "5_4"

          END as [MAPLOC],

          CASE T.N

              WHEN 1 THEN [Data$].[1_1]

              WHEN 2 THEN [Data$].[1_2]

              WHEN 3 THEN [Data$].[1_3]

              WHEN 4 THEN [Data$].[1_4]

              WHEN 5 THEN [Data$].[2_1]

              WHEN 6 THEN [Data$].[2_2]

              WHEN 7 THEN [Data$].[2_3]

              WHEN 8 THEN [Data$].[2_4]

              WHEN 9 THEN [Data$].[3_1]

              WHEN 10 THEN [Data$].[3_2]

              WHEN 11 THEN [Data$].[3_3]

              WHEN 12 THEN [Data$].[3_4]

              WHEN 13 THEN [Data$].[4_1]

              WHEN 14 THEN [Data$].[4_2]

              WHEN 15 THEN [Data$].[4_3]

              WHEN 16 THEN [Data$].[4_4]

              WHEN 17 THEN [Data$].[5_1]

              WHEN 18 THEN [Data$].[5_2]

              WHEN 19 THEN [Data$].[5_3]

              WHEN 20 THEN [Data$].[5_4]

          END as [Control_Weight],

          [Data$].[match_id] as [match_id],

          [Data$].[elapsed_time] as [elapsed_time]

      FROM [Data$]

          CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) AS T(N)

       

      The first one works but the second one generates the error "Unrecognized Keyword WHEN". Am I not allowed to use WHEN for Excel SQL Queries or have I made some other mistake? If I can not use WHEN how can I accomplish this transformation? I can not do it in the original Excel document because it exceeds Excels maximum Column count.

       

      Thanks