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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy