怎么显示结果为:吴道祥 时间 时间 时间SQL 竖向变横向
解决方案 »
- 求sql写法
- 求一个存储过程,插入数据时判断是否有该条记录,若有则无操作,若无插入数据
- 请问如何在BAT文件中编写执行某个数据库的某个存储过程
- LiangCK好样的,你是我们学习的榜样,快来接分
- "JOIN"附近语法错误,简单的,快啊,立即给分
- 求助!!!sqlserver2000数据转到sqlserver7.0要怎么实现啊??
- 相同查询结果不同SQL语句,如何判断其效率?
- 用eclipse连接msserver2005连接不上,我的数据库设置的是本地连接,求大神帮忙,程序没错!
- ……怎样从一个表中得到字段的名称……?
- 我想知道我的数据库有没有被黑?
- Mssql 性能优化问题
- xp连接windows server2003上的sql server2008不稳定,如何解决!
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-07 13:49:04
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([datetime] datetime,[name] varchar(1))
insert [huang]
select '2012-11-10','a' union all
select '2013-11-10','a'
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([name])+'=max(case when [name]='+quotename([name],'''')+' then [datetime] else 0 end)'
from [huang]
exec('select [name]'+@s+' from [huang] group by [name]')
----------------结果----------------------------
/*
name a a
---- ----------------------- -----------------------
a 2013-11-10 00:00:00.000 2013-11-10 00:00:00.000
*/
create table xz
(col1 varchar(15),col2 varchar(10))insert into xz
select '2012-11-10','吴道祥' union all
select '2012-11-13','吴道祥' union all
select '2012-12-03','吴道祥' union all
select '2012-12-04','吴道祥' union all
select '2012-12-13','吴道祥'
declare @tsql varchar(6000)select @tsql=isnull(@tsql+',','')+'['+rtrim(number)+']'
from master.dbo.spt_values
where type='P' and number between 1 and
(select max(c) from (select count(1) 'c' from xz group by col2) t)select @tsql='select col2,'+@tsql+'
from (select col1,col2,
row_number() over(partition by col2 order by getdate()) ''rn''
from xz) t
pivot(max(col1) for rn in('+@tsql+')) p '
exec(@tsql)/*
col2 1 2 3 4 5
---------- --------------- --------------- --------------- --------------- ---------------
吴道祥 2012-11-10 2012-11-13 2012-12-03 2012-12-04 2012-12-13(1 row(s) affected)
*/
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[f_Name](@Name varchar(50))
RETURNS varchar(max)
AS
BEGIN
DECLARE @str varchar(max)
SET @str = ''
select @str=@str+';'+skill from Table_test where name=@Name
RETURN @str
ENDselect distinct name ,[dbo].[f_Name](name)skill from Table_test表中有name和skill两个字段