多行变一行（整理总结）

谁践踏了优雅 2022-06-12 08:50 234阅读 0赞

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