Splitting out Data 1

[jhall01]

I need to break out data from one column into different columns. IE.

Table: Hist_tbl
Columns: CustID, Minutes, Month

CustId | Minutes | Month
------------------------------
111 | 1000 | March
111 | 950 | April
111 | 850 | May
222 | 200 | March
222 | 300 | April
etc.


needs to break out like this

Query: Hist_qry
Table pulled from: Hist_tbl

CustID | March_min | April_min | May_min | etc
------------------------------------------------
111 | 1000 | 950 | 850 | etc
222 | 200 | 300 | 500 | etc

Is there a way to break this out in one simple query using SQL? The Hist_tbl contains yr 2003 data only so no months per customer will be duplicated.

 

[dnoeth]

This is a common crosstab query and quite efficient:

SELECT
CustID,
MAX(CASE WHEN "Month" = 1 THEN Minutes END) AS Jan_min,
MAX(CASE WHEN "Month" = 2 THEN Minutes END) AS Feb_min,
...
SUM(Minutes) AS Year_min
FROM
Hist_tbl
GROUP BY CustID
;

If (CustID,Month) is not unique use SUM instead of MAX.

Dieter

 

[user3000]

Without using Case, we can do it thru this sql...

select
distinct h.cust_id 'cust',
s1.mins 'March',
s2.mins 'April',
s3.mins 'may'
from
hist_tbl h,
(
select
cust_id,
mins,
mons
from
hist_tbl
where
mons = 'march'
) as s1,
(
select
cust_id,
mins,
mons
from
hist_tbl
where
mons = 'april'
) as s2,
(
select
cust_id,
mins,
mons
from
hist_tbl
where
mons = 'may'
) as s3
where
h.cust_id = s1.cust_id
and h.cust_id = s2.cust_id
and h.cust_id = s3.cust_id

(Note: This will work perfectly iff values for all the month for a customer is specified, if not slight modification has to be made)

-User3000

 

[BillDHS]

Stick with Case, it is more efficient. I do think that dnoeth left out a table you will need that defines Jan as 1, Feb as 2, Mar as 3, so that the column order is managed.

 

[jhall01]

Thanks Deiter and Bill. I actually figured that out. I am new to SQL but soak things up like a sponge. The data provided is not the data i am using so i just needed an example of how to do it and this example worked perfect.

Actually it worked better than perfect. It cut out several hours of work for our team and cut the pull time down to about 30 seconds...compared to 14 minutes before.

Thanks so much!!!

 

[dnoeth]

BillDHS: "I do think that dnoeth left out a table you will need that defines Jan as 1, Feb as 2, Mar as 3"

You're right, but no need or an extra table ;-)
I usually use EXTRACT(MONTH FROM datecol) = 1, so this must be

MAX(CASE WHEN "Month" = 'January' THEN Minutes END)

Dieter