assuming that you know all the possible values of the TYPE column, you can do
this with corelated subqueries:
CREATE TABLE t
(
datecol NUMBER NOT NULL,
loc CHAR(2) NOT NULL,
quantity NUMBER NOT NULL,
TYPE CHAR(2) NOT NULL
);
INSERT INTO t
(datecol, loc, quantity, TYPE
)
VALUES (20050706, 'NS', 14, 'AP'
);
INSERT INTO t
(datecol, loc, quantity, TYPE
)
VALUES (20050706, 'NS', 1, 'PL'
);
INSERT INTO t
(datecol, loc, quantity, TYPE
)
VALUES (20050706, 'NS', 21, 'WB'
);
INSERT INTO t
(datecol, loc, quantity, TYPE
)
VALUES (20050706, 'SM', 104, 'AP'
);
INSERT INTO t
(datecol, loc, quantity, TYPE
)
VALUES (20050706, 'SM', 39, 'PL'
);
INSERT INTO t
(datecol, loc, quantity, TYPE
)
VALUES (20050706, 'SM', 291, 'WB'
);
COMMIT;
SELECT DISTINCT datecol, loc,
(SELECT SUM (quantity)
FROM t innert
WHERE innert.datecol = t.datecol
AND innert.loc = t.loc
AND TYPE = 'AP') ap,
(SELECT SUM (quantity)
FROM t innert
WHERE innert.datecol = t.datecol
AND innert.loc = t.loc
AND TYPE = 'PL') pl,
(SELECT SUM (quantity)
FROM t innert
WHERE innert.datecol = t.datecol
AND innert.loc = t.loc
AND TYPE = 'WB') wb
FROM t;
> Without commenting on the possible double entendre of the subject; is
> there an easy way to convert rows of a query into columns.
>
> I would like to take something that looks like this:
> DATE LOC # TYPE
> -------- -- --- ----
> 20050706 NS 14 AP
> 20050706 NS 1 PL
> 20050706 NS 21 WB
> 20050706 SM 104 AP
> 20050706 SM 39 PL
> 20050706 SM 291 WB
>
> And make it look like this:
> DATE LOC AP PL WB
> -------- --- --- --- ---
> 20050706 NS 14 1 21
> 20050706 SM 104 39 291
>
> If it matters this is for a CF6.1 & Oracle system.
>
> --------------
> Ian Skinner
> Web Programmer
> BloodSource
> www.BloodSource.org
> Sacramento, CA
>
> "C code. C code run. Run code run. Please!"
> - Cynthia Dunning
>
> Confidentiality Notice: This message including any
> attachments is for the sole use of the intended
> recipient(s) and may contain confidential and privileged
> information. Any unauthorized review, use, disclosure or
> distribution is prohibited. If you are not the
> intended recipient, please contact the sender and
> delete any copies of this message.
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215256
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
