Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Location
    Portland, OR
    Posts
    8

    Unanswered: How do I display 2 column table as a 8 column table

    Hi, I have a problem I am trying to solve and I cant find the solution anywhere.

    I have the following 3 column data:

    1,COURT_DATE, 2008/10/10
    1,DOCKET_NUMBER, 08-TRAF-000
    1,INCIDENT , 60413
    1,OFFENSE_0, SEATBELT REQUIRED
    1,OFFENSE_1, DRIVING UNDER SUSPENSION;
    1,OFFENSE_2, SPEEDING 52/45;
    1,NAME_FIRST, JOHN
    1,NAME_LAST, DOE
    2,COURT_DATE, 2008/10/10
    2,DOCKET_NUMBER, 08-TRAF-998
    2,INCIDENT , 60416
    2,OFFENSE_0, NO SEATBELT
    2,OFFENSE_1, EXPIRED MVI
    2,OFFENSE_2, SPEEDING 44/35
    2,OFFICER , JOHNSON
    2,NAME_FIRST, NANCY
    2,NAME_LAST, SMITH

    But I want to display it in a 8 column table like so:

    Column Names
    COURT_DATE,DOCKET_NUMBER,INCIDENT,OFFENSE_0,OFFENS E_1,OFFENSE_2,OFFICER,FIRST_NAME,LAST_NAME
    ========
    2008/10/10,08-TRAF-000,60413,SEATBELT REQUIRED,DRIVING UNDER SUSPENSION,SPEEDING 52/45, ,JOHN,DOE
    2008/10/10,08-TRAF-998,60416,NO SEATBELT,EXPIRED MVI,SPEEDING 44/35,JOHNSON,NANCY,SMITH
    Last edited by djangofan; 04-23-09 at 20:43.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Search for "pivot table".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2009
    Location
    Portland, OR
    Posts
    8
    That kinda helps but since I obviously dont need to SUM any data, I can't find any example on the net of how to do it without a SUM. I couldnt find a single example of how a PIVOT command would work without having a SUM inside the parenthesis. I suppose I could use a COUNT instead of SUM, but I dont see why PIVOT requires a SUM,AVG, or COUNT column in the result.

    I just want to convert the table from 1 dimension linear into 2 dimension grid without computing anything.

    I suspect it would be easier to do it with some kind of for loop in a stored procedure?
    Last edited by djangofan; 04-24-09 at 15:57.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not SUM, MAX
    Code:
    SELECT id
         , MAX(court_date) AS court_date
         , MAX(docket_number) AS docket_number
         , ...
      FROM ( SELECT id
                  , CASE WHEN col2 = 'COURT_DATE'
                         THEN col3 END AS court_date
                  , CASE WHEN col2 = 'DOCKET_NUMBER'
                         THEN col3 END AS docket_number
                  , ...
               FROM daTable
           ) AS d
    GROUP
        BY id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2008
    Posts
    3

    Question How to Manipulate Cross Tab Data Within Sql

    I've used Rudy's help on how to create a cross-tab query (Awesome!!). However, it brings me 50% of what I need. I can create the field "Sales" and I can create the field "Demand" (both of which were in separate records), but because the data is too much, and I only want to see where Sales <> Demand, I need to filter on these created columns. Is there a way I can filter on that condition within a single pass SQL?

    Here's what I have:

    Code:
    SELECT SKU, YR, WK, 
    SUM(CASE WHEN ID = 13 THEN VALUE ELSE 0 END) AS SALES,
    SUM(CASE WHEN ID = 35 THEN VALUE ELSE 0 END) AS DEMAND
    
    FROM DATABASE.FLEXIBLEROWTABLE 
    
    WHERE YR = 2009
    
    GROUP BY SKU, YR, WK
    In this case, I do want to sum, as I'm summing across locations. However, in my perfect world (where I'm so NOT!) I'd love to (read: need to....)

    1. Calculate DEMAND-SALES AS LOST_SALES
    2. Bring back records where LOST_SALES > 0 or where DEMAND<>SALES

    Thanks,

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT SKU, YR, WK
    ,  SUM(CASE WHEN ID = 13 THEN VALUE ELSE 0 END) AS SALES
    ,  SUM(CASE WHEN ID = 35 THEN VALUE ELSE 0 END) AS DEMAND
    ,  SUM(CASE WHEN ID = 35 THEN VALUE ELSE 0 END) 
    -  SUM(CASE WHEN ID = 13 THEN VALUE ELSE 0 END) AS LOST_SALES
       FROM DATABASE.FLEXIBLEROWTABLE 
       WHERE YR = 2009
       GROUP BY SKU, YR, WK
       HAVING SUM(CASE WHEN ID = 13 THEN VALUE ELSE 0 END) 
    <> SUM(CASE WHEN ID = 35 THEN VALUE ELSE 0 END)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT SKU, YR, WK
         , SALES
         , DEMAND
         , DEMAND - SALES AS LOST_SALES
      FROM (
           SELECT SKU, YR, WK
                , SUM(CASE WHEN ID = 13 THEN VALUE ELSE 0 END) AS SALES
                , SUM(CASE WHEN ID = 35 THEN VALUE ELSE 0 END) AS DEMAND
             FROM DATABASE.FLEXIBLEROWTABLE 
            WHERE YR = 2009
           GROUP BY SKU, YR, WK
           ) AS DATA
     WHERE SALES <> DEMAND
    substantially neater, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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