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

    Convert the columns into rows

    select 1, convert(char(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
    b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and '2015-12-30 00:00:00.000'
    order by b.cbm_batch_start_dt asc

    When i run 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

    But i want the output as follows

    REO
    10 Aug 2015 21 Aug 2015
    24 Aug 2015 04 Sep 2015
    14 Dec 2015 25 Dec 2015

    for getting output what changes i have to made in my above sql query.
  • #764563
    you can use PIVOT here, SQL Server has a PIVOT relational operator to turn the unique values of a specified column from multiple rows into multiple column values in the output (cross-tab), effectively rotating a table.
    look at below sample
    http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows
    http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query#xx4719222xx

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #764577
    Hi rao,
    Try following query.
    ;WITH ResultSet
    AS(
    SELECT * FROM (SELECT course FROM tb_rankwise_coursesettings) TTable
    UNPIVOT (Output FOR N IN (course))PPiVot)
    SELECT Output , '' from ResultSet Union all
    SELECT convert(char(12),b.cbm_batch_start_dt,106) as Startdt, convert(char(12),b.cbm_batch_end_dt,106) as Enddt
    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
    b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and '2015-12-30 00:00:00.000'
    Hope it helps.
    Regards,
    Shashikant Gurav
    shashikantgurav22@gmail.com


Sign In to post your comments