我试过但它不能将列转换成行 [英] I tried but it is not working convert columns into rows
问题描述
我试过的查询如下
选择1,将(char(12),b.cbm_batch_start_dt,106)转换为Startdt,
将(char(12),b.cbm_batch_end_dt,106)转换为Enddt,a.course作为课程从tb_rankwise_coursesettings作为a,CO_BATCH_MASTER作为b
其中a.course = b.cmn_minor_code和a.Rank ='CHENGR'和b.cbm_active<> 'd'和
b.cbm_batch_start_dt介于'2015-08-01 00:00:00.000'和'2015-12-30 00:00:00.000'
订单by b.cbm_batch_start_dt asc
当我运行上面的查询输出如下
Startdt Enddt Course
2015年8月10日2015年8月21日REO
2015年8月24日2015年9月4日REO
2015年12月14日2015年12月25日REO
但我希望输出如下
REO
2015年8月10日2015年8月21日
2015年8月24日2015年9月4日
2015年12月14日2015年12月25日
为了获得高于输出我写下如下查询
声明@ t1表(id int, startdt varchar(50),enddt varchar(50),course varchar(max))
插入@ t1
选择1,转换(char(12),b。 cbm_batch_start_dt,106)作为Startdt,
convert(char(12),b.cbm_batch_end_d t,106)作为Enddt,a.course作为课程从tb_rankwise_coursesettings作为a,CO_BATCH_MASTER作为b
其中a.course = b.cmn_minor_code和a.Rank ='CHENGR'和b.cbm_active< > 'd'和a.Active<> 'd'和b.cbm_batch_start_dt介于'2015-08-01 00:00:00.000'和'2015-12-30 00:00:00.000'
订购b.cbm_batch_start_dt asc
FROM @ t1
unpivot
{
REO
FOR [course] IN(REO )
} unpiv;
但是当我运行上面的查询错误时如下
关键字'FROM'附近的语法不正确
上述查询中的错误是什么。
我尝试了什么:
我试过的查询如下
选择1,将(char(12),b.cbm_batch_start_dt,106)转换为Startdt,
convert(char(12),b.cbm_batch_end_dt,106)作为Enddt,a.course作为课程从tb_rankwise_coursesettings作为a,CO_BATCH_MASTER作为b
其中a.course = b.cmn_minor_code和a.Rank ='CHENGR'和b.cbm_active<> 'd'和
b.cbm_batch_start_dt介于'2015-08-01 00:00:00.000'和'2015-12-30 00:00:00.000'
订单by b.cbm_batch_start_dt asc
当我运行上面的查询输出如下
Startdt Enddt Course
2015年8月10日2015年8月21日REO
2015年8月24日2015年9月4日REO
2015年12月14日2015年12月25日REO
但我希望输出如下
REO
2015年8月10日2015年8月21日
2015年8月24日2015年9月4日
2015年12月14日2015年12月25日
为了获得高于输出我写下如下查询
声明@ t1表(id int, startdt varchar(50),enddt varchar(50),course varchar(max))
插入@ t1
选择1,转换(char(12),b。 cbm_batch_start_dt,106)作为Startdt,
convert(char(12),b.cbm_batch_end_d t,106)作为Enddt,a.course作为课程从tb_rankwise_coursesettings作为a,CO_BATCH_MASTER作为b
其中a.course = b.cmn_minor_code和a.Rank ='CHENGR'和b.cbm_active< > 'd'和a.Active<> 'd'和b.cbm_batch_start_dt介于'2015-08-01 00:00:00.000'和'2015-12-30 00:00:00.000'
订购b.cbm_batch_start_dt asc
FROM @ t1
unpivot
{
REO
FOR [course] IN(REO )
} unpiv;
但是当我运行上面的查询错误时如下
关键字'FROM'附近的语法不正确
上述查询中的错误是什么。
选择 ' ScriptName',scriptName 来自 table
union 全部
选择 ' ScriptCode',scriptCode from table
union all
选择 ' 价格',价格来自 表
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.
select 'ScriptName', scriptName from table union all select 'ScriptCode', scriptCode from table union all select 'Price', price from table
这篇关于我试过但它不能将列转换成行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!