求一条行转列的SQL语句

[七块五]

现在表格式是

cname        kdate     mintime     maxtime
名字1        2011-9-1  8:00        17:30
名字2        2011-9-2  9:00        17:00
...          ...       ...         ...

要换成

          2011-9-1             2011-9-2     。。。
名字1     maxtime-mintime      maxtime-mintime
...       ...                  ...

[-晴天]

查论坛上的动态行转列例程.

[--小F--]

select
  cname,
  max(case when convert(varchar(10),kdate,120)='2011-9-1' then ltrim(mintime)+'-'+ltrim(maxtime) else '' end) as '2011-9-1' ,
...
from
  tb
group by
  cname

[七块五]

原来表的结构

CREATE TABLE [dbo].[kaoqin](
	[cname] [nvarchar](50) NULL,
	[yy_mm] [nvarchar](50) NULL,
	[mintime] [nvarchar](50) NULL,
	[maxtime] [nvarchar](50) NULL
) ON [PRIMARY]

现在我找了个SQL语句

declare @sql varchar(8000)
set @sql = 'select cname '
select @sql = @sql + ' , max(case yy_mm when ''' + yy_mm + ''' then mintime else null end) [' + yy_mm + ']'
from (select distinct yy_mm from kaoqin) as a
set @sql = @sql + ' from kaoqin group by cname'
exec(@sql) 

现提示对象名 'kaoqin' 无效，请帮忙查看下问题出在什么地方

还有我需要的是maxtime-mintime,那是不是

then mintime else null end

这里改掉就可以了

[--小F--]

declare @sql varchar(8000)
set @sql = 'select cname '
select @sql = @sql + ' , max(case yy_mm when ''' + yy_mm + ''' then ltrim(maxtime)+ltrim(mintime) else '''' end) [' + yy_mm + ']'
from (select distinct yy_mm from kaoqin) as a
set @sql = @sql + ' from kaoqin group by cname'
exec(@sql) 

[--小F--]

你是不是在KAOQIN表所在的数据库操作的哦 ？

[七块五]

[Quote=引用 5 楼 fredrickhu 的回复:]

你是不是在KAOQIN表所在的数据库操作的哦 ？
[/Quote]

是的啊。

会不会是因为express版本的数据库问题，前两天就碰到过莫名其妙的问题，第二天sql语句一字不改地又好了

[koumingjie]

CREATE TABLE [dbo].[kaoqin](
[cname] [nvarchar](50) NULL,
[yy_mm] [nvarchar](50) NULL,
[mintime] [nvarchar](50) NULL,
[maxtime] [nvarchar](50) NULL
)

insert kaoqin
select '名字1','2011-9-1','8:00','17:30' union all
select '名字2','2011-9-2','9:00','17:00' union all
select '名字1','2011-9-3','10:00','17:30' union all
select '名字1','2011-9-4','8:30','17:30'

declare @sql varchar(8000)
set @sql = 'select cname '
select @sql = @sql + ' , max(case yy_mm when ''' + yy_mm + ''' then ltrim(maxtime) + ''-'' + ltrim(mintime) else '''' end) [' + yy_mm + ']'
from (select distinct yy_mm from kaoqin) as a
set @sql = @sql + ' from kaoqin group by cname'
exec(@sql)


cname 2011-9-1 2011-9-2 2011-9-3 2011-9-4
-------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------
名字1 17:30-8:00 17:30-10:00 17:30-8:30
名字2 17:00-9:00

(2 行受影响)

[koumingjie]

CREATE TABLE [dbo].[kaoqin](
    [cname] [nvarchar](50) NULL,
    [yy_mm] [nvarchar](50) NULL,
    [mintime] [nvarchar](50) NULL,
    [maxtime] [nvarchar](50) NULL
)

insert kaoqin
select '名字1','2011-9-1','8:00','17:30' union all
select '名字2','2011-9-2','9:00','17:00' union all
select '名字1','2011-9-3','10:00','17:30' union all
select '名字1','2011-9-4','8:30','17:30' 

declare @sql varchar(8000)
set @sql = 'select cname '
select @sql = @sql + ' , max(case yy_mm when ''' + yy_mm + ''' then ltrim(maxtime) + ''-'' + ltrim(mintime) else '''' end) [' + yy_mm + ']'
from (select distinct yy_mm from kaoqin) as a
set @sql = @sql + ' from kaoqin group by cname'
exec(@sql) 


cname                                              2011-9-1                                                                                              2011-9-2                                                                                              2011-9-3                                                                                              2011-9-4
-------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------
名字1                                                17:30-8:00                                                                                                                                                                                                  17:30-10:00                                                                                           17:30-8:30
名字2                                                                                                                                                      17:00-9:00                                                                                                                                                                                                  

(2 行受影响)

[七块五]

贴上我全部的SQL语句，请各位朋友帮忙看看这个到底是什么问题

drop table #kaoqin

create table #kaoqin
(
cname nvarchar(50),
yy_mm nvarchar(50),
mintime nvarchar(50),
maxtime nvarchar(50)
)

insert into #kaoqin (cname,yy_mm,mintime,maxtime) select
x.usralias, CONVERT(varchar(10), y.OpenTime, 120) AS yy_mm, 
CONVERT(varchar(8), MIN(y.OpenTime), 8) AS mintime, 
CONVERT(varchar(8), MAX(y.OpenTime), 8) AS maxtime
FROM dbo.ShhmJanitorRecordhis y right join shhmcmscardinfo x on x.cardid=y.cardid
GROUP BY x.usralias, CONVERT(varchar(10), y.OpenTime, 120)


--select * from #kaoqin
declare @sql varchar(8000)
set @sql = 'select cname '
select @sql = @sql + ' , max(case yy_mm when ''' + yy_mm + ''' then ltrim(maxtime) + ''-'' + ltrim(mintime) else '''' end) [' + yy_mm + ']'
from (select distinct yy_mm from #kaoqin) as a
set @sql = @sql + ' from #kaoqin group by cname'
exec(@sql) 

提示是

(123 行受影响)
消息 156，级别 15，状态 1，第 1 行
关键字 'from' 附近有语法错误。