Display SQL Data From Row to Column

Question

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?

If you're on SQL Server (which RDBMS?) the PIVOT command will do this for you. — DWright
– DWright, Commented Jan 11, 2013 at 0:43
Otherwise, if you are on, say, Mysql, this steps you through how: stackoverflow.com/questions/7674786/mysql-pivot-table. — DWright
– DWright, Commented Jan 11, 2013 at 0: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
– goh6319, Commented Jan 11, 2013 at 1:35
@DWright the pivot function full fill my request. Thank you for ur help and have a nice day. — goh6319
– goh6319, Commented Jan 11, 2013 at 2:04

Taryn · Accepted Answer · 2013-01-11 01:34:43Z

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

edited Jan 11, 2013 at 1:34

answered Jan 11, 2013 at 1:26

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bobs Over a year ago

OP has distinct count in the example. Do you have to account for that in your solutions?

goh6319 Over a year ago

@bobs yes. I need to account for the distinct in my solution.

Taryn Over a year ago

Edited to include DISTINCT

goh6319 Over a year ago

@bobs. Woah~my friend this is impressive and it really working !! Thank a lot.

Collectives™ on Stack Overflow

Display SQL Data From Row to Column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related