narasiman rao

narasiman rao

  • NA
  • 519
  • 765.7k

i tried convert the columns into rows in sql server

Feb 13 2016 5:13 AM
My tried query as follows

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 above ouput i write the query as follows



declare @t1 table(id int,startdt varchar(50),enddt varchar(50),course varchar(max))
insert into @t1
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 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'
order by b.cbm_batch_start_dt asc
FROM @t1
unpivot
{
REO
FOR [course] IN (REO)
} unpiv;


but when i run the above query error as follows

Incorrect syntax near the keyword 'FROM'

what is the mistake in my above query.

What I have tried:

My tried query as follows

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 above ouput i write the query as follows



declare @t1 table(id int,startdt varchar(50),enddt varchar(50),course varchar(max))
insert into @t1
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 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'
order by b.cbm_batch_start_dt asc
FROM @t1
unpivot
{
REO
FOR [course] IN (REO)
} unpiv;


but when i run the above query error as follows

Incorrect syntax near the keyword 'FROM'

what is the mistake in my above query.

Answers (1)