多行变一行（整理总结）

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

 
--案例1
--> 测试数据: [one]（多行变一行并实现部分行列转换）
if object