You must Sign In to post a response.
  • Category: SQL Server

    Unpivot query error occurs

    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
  • #764586
    Hi,
    Here inside UNPIVOT statement in clause IN(ColumnName), you must have to specify a ColumnName which is present inside your select statement.
    Your enhanced query will be:
    SELECT Startdt,Enddt, Course1 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 [Course1] IN (Course)) unpiv

    but you will not get desired output using this query. Correct query I had posted on this thread:
    http://www.dotnetspider.com/forum/344036-Convert-the-columns-into-rows.aspx

    Hope this will help you.
    Regards,
    Shashikant Gurav
    shashikantgurav22@gmail.com


Sign In to post your comments