Results 1 to 2 of 2

Thread: SQL Optimize

  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: SQL Optimize

    I have this 4 row table:

    +---+--------+
    | ID | WeekNo |
    +---+--------+
    | 1 | 201209 |
    | 2 | 201210 |
    | 3 | 201211 |
    | 4 | 201212 |
    +---+--------+

    I use the code below to transform the table into this:

    +--------+--------+--------+--------+
    | WK1 | WK2 | WK3 | WK4 |
    +--------+--------+--------+--------+
    | 201209 | 201210 | 201211 | 201212 |
    +--------+--------+--------+--------+

    SELECT 'WK1'= CASE ID WHEN 1 THEN BIWeekNo END,
    'WK2'= CASE ID WHEN 2 THEN BIWeekNo END,
    'WK3'= CASE ID WHEN 3 THEN BIWeekNo END,
    'WK4'= CASE ID WHEN 4 THEN BIWeekNo END
    INTO #tempRPTWeek1
    FROM tempRPTWeek

    SELECT MAX(WK1) AS WK1,MAX(WK2) AS WK2,MAX(WK3) AS WK3,MAX(WK4) AS WK4
    INTO #tempRPTWeek2 FROM #tempRPTWeek1

    SELECT * FROM #tempRPTWeek2
    Is there a better way to do this using only 1 SQL statement?

    TIA

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Have a look at the Pivot command. I am not sure, but it may require an aggregate function to be involved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •