---------------------------------------------------------------- -- 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 '
SET ANSI_NULLS ON 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两个字段
-- 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两个字段