narasiman rao

narasiman rao

  • NA
  • 519
  • 747.2k

Unpivot query error

Feb 14 2016 10:54 PM
select
CONVERT(varchar(12), b.cbm_batch_start_dt, 106) AS Startdt ,CONVERT(CHAR(12), b.cbm_batch_end_dt, 106) AS Enddt ,a.course AS Course FROM tb_rankwise_coursesettings AS a ,CO_BATCH_MASTER AS b WHERE a.course = b.cmn_minor_code
AND a.Rank = 'CHENGR' AND b.cbm_active <> 'D' and a.active <> 'd'
AND b.cbm_batch_start_dt BETWEEN '2015-08-01 00:00:00.000'
AND '2015-12-30 00:00:00.000'

When i execute the above query output as follows

startdt Enddt Course
10 Aug 2015 21 Aug 2015 REO
24 Aug 2015 04 Sep 2015 REO
14 Dec 2015 25 Dec 2015 REO

From the above output i want excepted output as follows
REO
10 Aug 2015 21 Aug 2015
24 Aug 2015 04 Sep 2015
14 Dec 2015 25 Dec 2015

for getting excepted output i written query as follows

SELECT Startdt,Enddt,Course FROM (select
CONVERT(varchar(12), b.cbm_batch_start_dt, 106) AS Startdt ,CONVERT(CHAR(12), b.cbm_batch_end_dt, 106) AS Enddt ,a.course AS Course FROM tb_rankwise_coursesettings AS a ,CO_BATCH_MASTER AS b WHERE a.course = b.cmn_minor_code AND a.Rank = 'CHENGR'
AND b.cbm_active <> 'd'
AND a.Active <> 'd'
AND b.cbm_batch_start_dt BETWEEN '2015-08-01 00:00:00.000'
AND <'2015-12-30 00:00:00.000')k
UNPIVOT(REO FOR [course] IN (REO) ) unpiv

When i execute the above query error as follows
Invalid column name 'REO'

The column name 'course' specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument

The column 'course' was specified multiple times for 'UNPIV'

please help me what is the mistake i made the above query

What I have tried:

select
CONVERT(varchar(12), b.cbm_batch_start_dt, 106) AS Startdt ,CONVERT(CHAR(12), b.cbm_batch_end_dt, 106) AS Enddt ,a.course AS Course FROM tb_rankwise_coursesettings AS a ,CO_BATCH_MASTER AS b WHERE a.course = b.cmn_minor_code
AND a.Rank = 'CHENGR' AND b.cbm_active <> 'D' and a.active <> 'd'
AND b.cbm_batch_start_dt BETWEEN '2015-08-01 00:00:00.000'
AND '2015-12-30 00:00:00.000'

When i execute the above query output as follows

startdt Enddt Course
10 Aug 2015 21 Aug 2015 REO
24 Aug 2015 04 Sep 2015 REO
14 Dec 2015 25 Dec 2015 REO

From the above output i want excepted output as follows
REO
10 Aug 2015 21 Aug 2015
24 Aug 2015 04 Sep 2015
14 Dec 2015 25 Dec 2015

for getting excepted output i written query as follows

SELECT Startdt,Enddt,Course FROM (select
CONVERT(varchar(12), b.cbm_batch_start_dt, 106) AS Startdt ,CONVERT(CHAR(12), b.cbm_batch_end_dt, 106) AS Enddt ,a.course AS Course FROM tb_rankwise_coursesettings AS a ,CO_BATCH_MASTER AS b WHERE a.course = b.cmn_minor_code AND a.Rank = 'CHENGR'
AND b.cbm_active <> 'd'
AND a.Active <> 'd'
AND b.cbm_batch_start_dt BETWEEN '2015-08-01 00:00:00.000'
AND <'2015-12-30 00:00:00.000')k
UNPIVOT(REO FOR [course] IN (REO) ) unpiv

When i execute the above query error as follows
Invalid column name 'REO'

The column name 'course' specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument

The column 'course' was specified multiple times for 'UNPIV'

please help me what is the mistake i made the above query