Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have db problem in transposing rows to columns. I am half way through the result but getting redundant data. My table:

EMP_ID  EMP_NAME   SAL_PAID
01      ABC        JAN
01      ABC        FEB
01      ABC        MAR
02      PQR        JAN
02      PQR        MAR
03      XYZ        FEB

Result Table:

EMP_ID  EMP_NAME   JAN    FEB    MAR    APR
01      ABC         Y      Y      Y      N
02      PQR         Y      N      Y      N
03      XYZ         N      Y      N      N

I have used case and then the result is as below:

EMP_ID  EMP_NAME   JAN    FEB    MAR    APR
01      ABC         Y      N      N      N
01      ABC         N      Y      N      N
01      ABC         N      N      Y      N
02      PQR         Y      N      N      N
02      PQR         N      N      Y      N
02      PQR         N      N      N      N
03      XYZ         N      Y      N      N
03      XYZ         N      N      N      N

I have tried this for quite a bit now. Thank you.

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

Telling us that you are using a CASE statement but not showing us the actual code that you're using doesn't help us much. Assuming that "Result Table" is the desired output and that you want to use the old-style MAX(CASE syntax to do your pivot rather than using the newer PIVOT operator

SELECT emp_id,
       emp_name,
       nvl( max( case when sal_paid = 'JAN' then'Y' else NULL end ), 'N' ) jan,
       nvl( max( case when sal_paid = 'FEB' then'Y' else NULL end ), 'N' ) feb,
       nvl( max( case when sal_paid = 'MAR' then'Y' else NULL end ), 'N' ) mar,
       nvl( max( case when sal_paid = 'APR' then'Y' else NULL end ), 'N' ) apr
  FROM your_table
 GROUP BY emp_id, emp_name
share|improve this answer
    
Thank you for the answer. This totally helped me in getting the concept right. Tables that I am using is too huge and had too many of the things that had to be grouped so I condensed it to show it this way. And sorry for the late reply. –  thanmai Aug 27 '13 at 6:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.