多行变一行代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】多行变一行
作者:zhaowenzhong / 发布于2013/3/26/ 837

	--案例1
	--> 测试数据: [one]（多行变一行并实现部分行列转换）
	if object_id('[one]') is not null drop table [one]
	create table [one] (month int,day int,money int)
	insert into [one]
	select 10,1,200 union all
	select 10,2,300 union all
	select 10,3,400 union all
	select 11,1,500 union all
	select 11,2,600 union all
	select 11,3,700

	--动态sql
	declare @sql varchar(8000)
	set @sql='select [month]'
	select @sql=@sql+',['+ltrim([day])+']=sum(case [day] when '''+ltrim([day])+''' then [money] else 0 end)'
	from (select distinct [day] from one)a
	set @sql=@sql+' from ss group by [month]'
	exec(@sql)

	--案例2
	IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID('[two]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1 )
	 DROP TABLE two
	CREATE TABLE two
	(id INT IDENTITY(1, 1)NOT NULL ,
	UserName VARCHAR(50) ,
	Code VARCHAR(50) NULL
	)
	
	SET IDENTITY_INSERT two ON
	INSERT two( id, UserName, code )VALUES ( 1, 'User1', 'A' )
	INSERT two( id, UserName, code )VALUES ( 1, 'User1', 'B' )
	INSERT two( id, UserName, code )VALUES ( 1, 'User2', 'C' )
	INSERT two( id, UserName, code )VALUES ( 1, 'User2', 'D' )
	INSERT two( id, UserName, code )VALUES ( 1, 'User2', 'E' )
	INSERT two( id, UserName, code )VALUES ( 1, 'User3', 'F' )
	INSERT two( id, UserName, code )VALUES ( 1, 'User3', 'G' )
	INSERT two( id, UserName, code )VALUES ( 1, 'User3', 'H' )
	INSERT two( id, UserName, code )VALUES ( 1, 'User3', 'I' )
	SET IDENTITY_INSERT two OFF

	--按某一列出结果的
	SELECT B.username ,LEFT(UserList, LEN(UserList) - 1) AS list
	FROM ( SELECT username ,
	 ( SELECT code + ',' FROM two WHERE username = A.username ORDER BY ID FOR XML PATH('')
	 ) AS UserList
	 FROM two A GROUP BY username
	 ) B


	--找符合条件的某一列数据(最后一列的值变一行）
	--法一
	SELECT LEFT(userlist, LEN(userlist) - 1) list
	FROM ( SELECT ( SELECT code + ',' FROM two WHERE 1 = 1 ORDER BY ID FOR XML PATH('')
	 ) AS userlist
	 ) B
	--案例3

	-- 1. 创建处理函数

	--drop table tb
	create table tb ([id] int,[value] nvarchar(1))
	Insert tb
	select 1,N'a' union all
	select 1,N'b' union all
	select 1,N'c' union all
	select 2,N'd' union all
	select 2,N'e' union all
	select 3,N'f'

	CREATE FUNCTION dbo.f_str(@id int)
	RETURNS varchar(8000)
	AS
	BEGIN
	 DECLARE @r varchar(8000)
	 SET @r = ''
	 SELECT @r = @r + ',' + value
	 FROM tb
	 WHERE id=@id
	 RETURN STUFF(@r, 1, 1, '')
	END
	GO
	--------函数方式（SQL 2000,2005,2008)
	Select distinct id,value=dbo.F_Str(id) from tb
	--go

	------------CTE(SQL 2005,2008)
	;with roy as(select id,value,row=row_number()over(partition by id order by id) from tb)
	,Roy2 as
	(select id,cast(value as nvarchar(100))value,row from Roy where row=1
	union all
	select a.id,cast(b.value+','+a.value as nvarchar(100)),a.row from Roy a join Roy2 b on a.id=b.id and a.row=b.row+1)
	select id,value from Roy2 a where row=(select max(row) from roy where id=a.id) order by id option (MAXRECURSION 0)

	--案例4

	create table tb
	(
	id int,
	type varchar(12)
	)
	go
	insert into tb
	select 1,'aa' union all
	select 2,'bb' union all
	select 3,'aa' union all
	select 4,'aa' union all
	select 5,'ab' union all
	select 6,'bb' union all
	select 7,'c' union all
	select 8,'ac'

	go
	create function Fn_GetType_STR(@typeid varchar(32))
	returns VARCHAR(1000)

	AS
	begin
	 declare @s varchar(1000)
	 select @s=isnull(@s+',' , '')+ cast(id as varchar) from tb where type =@typeid
	 return @s
	end

	select DISTINCT TYPE,STR=dbo.Fn_GetType_STR(type) from tb
	/*
	aa 1,3,4
	ab 5
	ac 8
	bb 2,6
	c 7
	*/

	------案例5(最通用的方式)
	declare @tb table (id int, value varchar(10))
	insert into @tb values(1, 'aa')
	insert into @tb values(1, 'bb')
	insert into @tb values(2, 'aaa')
	insert into @tb values(2, 'bbb')
	insert into @tb values(2, 'ccc')

	select id , [value]=
	stuff((select ','+[value] from @tb t where id =tv.id for xml path('')), 1, 1, '')
	from @tb as tv
	group by id
	/*
	id ccname
	1 aa,bb
	2 aaa,bbb,ccc
	*/

	SELECT *FROM (SELECT DISTINCT Id FROM @tb) A
	OUTER APPLY(
	 SELECT [values]= STUFF(REPLACE(REPLACE(
	 ( SELECT value FROM @tb N
	 WHERE id = A.id
	 FOR XML AUTO
	 ), '<N value="', ','), '"/>', ''), 1, 1, '')
	)as N
	----------------
	if object_id('[tbl]') is not null drop table [tbl]
	create table [tbl]([id] int)
	insert [tbl]
	select 1 union all
	select 2 union all
	select 3 union all
	select 4
	declare @str varchar(20)
	set @str=''
	select @str=@str+','+LTRIM(id) from tbl
	select RIGHT(@str,LEN(@str)-1) as new

	--------案例6 动态SQL 实现方式

	create table tb(col varchar(20))
	insert tb values ('a')
	insert tb values ('b')
	insert tb values ('c')
	insert tb values ('d')
	insert tb values ('e')
	go

	--方法一
	declare @sql varchar(1000)
	set @sql = ''
	select @sql = @sql + t.col + ',' from (select col from tb) as t
	set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
	exec(@sql)
	/*
	result
	----------
	a,b,c,d,e,
	*/

	--方法二
	declare @output varchar(8000)
	select @output = coalesce(@output + ',' , '') + col from tb
	print @output
	/*
	a,b,c,d,e
	*/

	方法三

	select (select ltrim(col)+',' from tb for xml path('')) as a---无条件：1列多行变一行
	drop table tb

	-------------有重复值的情况
	表a
	name num
	aa 1
	bb 2
	cc 4
	dd 4
	ee 2
	一条语句实现如下:
	name
	aa,bb,cc,dd,ee
	DECLARE @STR VARCHAR(8000)
	SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS T
	SELECT @STR
试试其它关键字
　多行变一行　
同语言下
可能有用的
zhaowenzhong贡献的其它代码(7)