How to convert rows to columns in oracle

We have data of attendance('ab'--> absent, null--> present) on different dates(d1, d2..)

select id, value from (
select 1 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
union all
select 2 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
union all
select 3 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
union all
select 4 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
union all
select 5 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
) 
UNPIVOT
(
value for value_type in(d1,d2,d3,d4,d5)
)

Later, you can take the count of it.

select id, count(value) from (
select 1 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
union all
select 2 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
union all
select 3 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
union all
select 4 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
union all
select 5 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual
) 
UNPIVOT
(
value for value_type in(d1,d2,d3,d4,d5)
)
group by id

Comments

Post a Comment

Popular posts from this blog

Table high water mark tips

  Question:  Can you please explain the concept of a table high water mark (HWM)?  I understand that the data is always below the high water mark.  Can you show a script to display the high water mark for a table? Answer:   The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.  Just as a lake has a high-water mark after a draught, an Oracle table has a high water mark that shows the greatest size of the table, the point at which it consumed the most extents. Also see this great script to  display all high water marks . As a table undergoes deletes and updates, rows shrink and table data blocks become empty.  For performance reasons, Oracle keeps the high water mark for a table rather than re-calculate the high water mark after blocks at the "end" of the table (the last extent) becomes empty. For example assume that you have a million row table that takes 30 seconds to read.  After deleting 900...
Read more

Create table of two consecutive months if date range belong to two months

select to_date('01-JUL-2019')+level-1 "JULY", CASE WHEN MONTHS_BETWEEN('09-AUG-2019','01-JUL-19')>1 THEN CASE WHEN LEVEL<=TO_NUMBER(TO_CHAR(TO_DATE('09-AUG-2019'),'DD')) THEN ADD_MONTHS('01-JUL-2019',1)+LEVEL-1 END END "August" FROM DUAL CONNECT BY LEVEL<=CASE WHEN MONTHS_BETWEEN('09-AUG-2019', '01-JUL-19')>1 THEN TO_NUMBER(TO_CHAR(LAST_DAY('01-JUL-2019'),'DD')) ELSE TO_DATE('09-AUG-2019')-TO_DATE('01-JUL-2019') +1 END ----------------------------------- ANOTHER METHOD ----------------------------------- SELECT "JULY", "AUGUST" FROM ( SELECT ROWNUM RN, CASE WHEN TO_CHAR(TO_DATE(MNTH),'MON')='JUL' THEN MONTH END "JULY" FROM ( SELECT TO_DATE('01-JUL-2019')+LEVEL-1 MNTH FROM DUAL CONNECT BY LEVEL<=TO_DATE('09-AUG-19')-T0_DATE('01-JUL-2019')+1 ...
Read more

Fill null values with last non-null amount - Oracle SQL

  I have a table that has 4 columns: Item, Year, Month, Amount. Some of the values for Amount are null and when that happens I want to fill those values in with the previous Amount value that is not null. I can easily do this with the LAG function when there is only one null value but when there are multiple in a row I am not sure how to approach it. Below is an example of what the table might look like with an added column for what I want to add in my query: Item | Year | Month | Amount | New_Amount AAA | 2013 | 01 | 100 | 100 AAA | 2013 | 02 | | 100 AAA | 2013 | 03 | 150 | 150 AAA | 2013 | 04 | 125 | 125 AAA | 2013 | 05 | | 125 AAA | 2013 | 06 | | 125 AAA | 2013 | 07 | | 125 AAA | 2013 | 08 | 175 | 175 I had two ideas which I can't seem to get to work to produce what I want. First I was going to use LAG but then I noticed when there are multiple null values in a row it won't satisfy that. Next I was going...
Read more