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

djangofan · #1 (**permalink**) 04-23-09, 19:30

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

I have the following 3 column data:

Quote:

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:

Quote:

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

stolze · #2 (**permalink**) 04-24-09, 06:43

Search for "pivot table".

r937 · #3 (**permalink**) 04-24-09, 08:18

or search for "crosstab query"

e.g. What is a cross tab query and how do you create one using a relational database?

djangofan · #4 (**permalink**) 04-24-09, 14:32

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?

r937 · #5 (**permalink**) 04-24-09, 15:39

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

ibcarolek · #6 (**permalink**) 06-10-09, 20:34

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,

Pat Phelan · #7 (**permalink**) 06-11-09, 00:03

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

r937 · #8 (**permalink**) 06-11-09, 00:56

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

Internet Services

Web Development

Digital Marketing

Consumer Tech