--案例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