July 5, 2008 at 11:53 am
Hi,
I have the script in SQL Server 2005 that it works very fine as pivot, but I want to use it also in Oracle 8i I tryed it but no success, how can I find solution in Oracle 8i!
there is the script:
CREATE VIEW [dbo].[PIVOT_week_year]
AS
SELECT year, weeknr, [IM4], [IM5], [IM6], [IM7], [IM8], [IM9], [EX1], [EX2], [EX3],
FROM
(
SELECT DATEPART(YEAR,DATE_KK) AS YEAR, DATEPART(WEEK,DATE_KK) AS WEEKNR, REG_INFO FROM DATATABLE
) O
PIVOT
(
COUNT(REG_INFO)
FOR REG_INFO IN ([IM4], [IM5], [IM6], [IM7], [IM8], [IM9], [EX1], [EX2], [EX3],
)
) PVT
Plz help how to write the correct code because I didn't find any solution yet!
Thanks!
July 8, 2008 at 4:17 am
Hi
Of the top of my head I would suggest the following for the SELECT. It should work, but I don't know the exact layout of your tables.
SELECT DATEPART(YEAR,DATE_KK) AS year,
DATEPART(WEEK,DATE_KK) AS weeknr,
SUM( CASE WHEN REG_INFO ='IM4' THEN 1 ELSE 0 END ) AS [IM4],
SUM( CASE WHEN REG_INFO ='IM5' THEN 1 ELSE 0 END ) AS [IM5],
SUM( CASE WHEN REG_INFO ='IM6' THEN 1 ELSE 0 END ) AS [IM6],
-- Repeat for all COUNTS
FROM DATATABLE
ORDER BY DATEPART(YEAR,DATE_KK), DATEPART(WEEK,DATE_KK)
GROUP BY DATEPART(YEAR,DATE_KK), DATEPART(WEEK,DATE_KK)
Use the SUM as a replacement for the COUNT. I think it will work, but I have not tested it or don't any real investigation.
Regards
Richard....
http://www.linkedin.com/in/gbd77rc
July 9, 2008 at 12:55 am
July 9, 2008 at 1:33 am
In Oracle 7 (the last version I had dealings with) I think you had to use something like
TO_DATE(Char,Format,Param)
So you might have to use something like
TO_DATE(TO_CHAR(DATE_KK),'DD-MON-RR','YYYY') AS YEAR
This would be for a DATE_KK = 01-AUG-08 and the TO_CHAR may not be necessary, I think the Year will be a character string rather than a number so if you require a number you may need to use the TO_NUMBER function.
Hope this helps.
Sandy
July 9, 2008 at 4:39 am
I try it with TO_CHAR but still not working ... because the same function I used on another view and it works very fine... here to create pivot not working!
I'm trying to find the solution
thnx anyway!
Dugi
July 9, 2008 at 9:50 am
Hi
Sorry about the original SQL, it was using MS SQL syntax and not Oracle. Anyway try this. I tested this on Oracle Express 10 and it looks like it will produce what you want.
SELECT TO_CHAR(DATE_KK,'yyyy') AS year, TO_CHAR(DATE_KK,'ww') AS weeknr,
SUM( CASE WHEN REG_INFO ='IM4' THEN 1 ELSE 0 END ) AS IM4,
SUM( CASE WHEN REG_INFO ='IM5' THEN 1 ELSE 0 END ) AS IM5,
SUM( CASE WHEN REG_INFO ='IM6' THEN 1 ELSE 0 END ) AS IM6,
SUM( CASE WHEN REG_INFO ='IM7' THEN 1 ELSE 0 END ) AS IM7,
SUM( CASE WHEN REG_INFO ='IM8' THEN 1 ELSE 0 END ) AS IM8
FROM DATATABLE GROUP BY DATE_KK ORDER BY 1,2
Regards
Richard....
http://www.linkedin.com/in/gbd77rc
July 9, 2008 at 12:04 pm
July 16, 2008 at 7:46 am
I forget it to post reply, I tested today and it works very fine!
Also I changed and I tested with function TO_CHAR, but not working the wrong things was [ ]!
Now works fine!
thnx for reply!
Dugi
July 16, 2008 at 8:03 am
richard.clarke (7/9/2008)
HiSorry about the original SQL, it was using MS SQL syntax and not Oracle. Anyway try this. I tested this on Oracle Express 10 and it looks like it will produce what you want.
SELECT TO_CHAR(DATE_KK,'yyyy') AS year, TO_CHAR(DATE_KK,'ww') AS weeknr,
SUM( CASE WHEN REG_INFO ='IM4' THEN 1 ELSE 0 END ) AS IM4,
SUM( CASE WHEN REG_INFO ='IM5' THEN 1 ELSE 0 END ) AS IM5,
SUM( CASE WHEN REG_INFO ='IM6' THEN 1 ELSE 0 END ) AS IM6,
SUM( CASE WHEN REG_INFO ='IM7' THEN 1 ELSE 0 END ) AS IM7,
SUM( CASE WHEN REG_INFO ='IM8' THEN 1 ELSE 0 END ) AS IM8
FROM DATATABLE GROUP BY DATE_KK ORDER BY 1,2
Regards
Richard....
Ooppssss Sorry ... it works but incorrect results I don't know why every column has value 1 and this is impossible because in SQL Server 2005 works like a charm counting Reg_Info so in this case in Ora no counting just flag info "1"
Is it from that CASE clause here:
COUNT(CASE WHEN REG_INFO = 'IM4' THEN 1 ELSE 0 END) AS IM4
???????
Simply no counting!
July 16, 2008 at 8:26 am
Another important thing to know is that I'm trying it in Oracle 8i - but not working! Is it any reason for the Oracle version!
Dugi
July 16, 2008 at 10:52 am
Hi Dugi,
You need SUM instead of COUNT here. The reason is that COUNT returns the number of rows found and the CASE statement will always return 1 row, but the value will be either 0 or 1. So if you have 6 rows and only 2 return 1 the COUNT will still be 6. That was the reason I put in SUM here as it will return 2.
Why this works on SQL is a bit weird, as it should behave the same.
I hope this helps.
Regards
Richard...
http://www.linkedin.com/in/gbd77rc
July 25, 2008 at 1:52 am
No, I do not have any results and I need Counting not Sum!
I posted the code what can i do in SQL Server and it works very nice, but I want the same results for the data in Oracle DB! But I didn't find any solution till know!
Thnx anyway!
Dugi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply