1

My SQL statement is list as below: 

SELECT Count(DISTINCT A.LM_PERSON_ID) AS HEAD_COUNT 
,A.LM_STATUS
,To_Char(A.LM_STATUS_CHANGE_DT,'YYYY') AS YEAR
,B.LM_COURSE_NAME AS COURSE_NAME

FROM LM_ENRLOLMENT A
,LM_COURSE_TBL B

WHERE A.LM_STATUS='COMP'
AND A.LM_COURSE_ID=B.LM_CI_ID                  

GROUP BY A.LM_STATUS_CHANGE_DT,LM_STATUS,B.LM_COURSE_NAME

The example output I would like to display was:

COURSE_NAME      2010      2011     2012
   A              4         5        1
   B              2         1        1
   C              6         0        3
   D              1         1        2

But the main problem I am facing is that LM_STATUS_CHANGE_DT is dynamic data. Is there anyone that can show me how I could do this?

Taryn
  • 242,637
  • 56
  • 362
  • 405
goh6319
  • 137
  • 1
  • 3
  • 20
  • 1
    If you're on SQL Server (which RDBMS?) the PIVOT command will do this for you. – DWright Jan 11 '13 at 00:43
  • Otherwise, if you are on, say, Mysql, this steps you through how: http://stackoverflow.com/questions/7674786/mysql-pivot-table. – DWright Jan 11 '13 at 00:45
  • @DWright hi,nice to meet you. I am using oracle SQL no Mysql. I will try using pivot function. Feedback to you later. By the ways, isn't possible i done in by using LISTAGG function? – goh6319 Jan 11 '13 at 01:35
  • @DWright the pivot function full fill my request. Thank you for ur help and have a nice day. – goh6319 Jan 11 '13 at 02:04

1 Answers1

2

You did not specify what RDBMS you are using but you should be able to use the following in all versions:

SELECT 
  B.LM_COURSE_NAME AS COURSE_NAME,
  count(DISTINCT case when To_Char(A.LM_STATUS_CHANGE_DT,'YYYY') = '2010' then A.LM_PERSON_ID end) as Year2010,
  count(DISTINCT case when To_Char(A.LM_STATUS_CHANGE_DT,'YYYY') = '2011' then A.LM_PERSON_ID end) as Year2011,
  count(DISTINCT case when To_Char(A.LM_STATUS_CHANGE_DT,'YYYY') = '2012' then A.LM_PERSON_ID end) as Year2012
FROM LM_ENRLOLMENT A
INNER JOIN LM_COURSE_TBL B
  ON A.LM_COURSE_ID=B.LM_CI_ID
WHERE A.LM_STATUS='COMP'
GROUP BY B.LM_COURSE_NAME

If you are using an RDBMS that has the PIVOT function (SQL Server 2005+/Oracle 11g+) then your code will be similar to this:

SELECT *
FROM
(
  SELECT DISTINCT B.LM_COURSE_NAME,
    To_Char(A.LM_STATUS_CHANGE_DT,'YYYY') As Year,
    A.LM_PERSON_ID
  FROM LM_ENRLOLMENT A
  INNER JOIN LM_COURSE_TBL B
    ON A.LM_COURSE_ID=B.LM_CI_ID
  WHERE A.LM_STATUS='COMP'
) src
PIVOT
(
  count(LM_PERSON_ID)
  for Year in ('2010', '2011', '2012')
) piv
Taryn
  • 242,637
  • 56
  • 362
  • 405