一个行列转换的问题（字符型的怎么转为列）？

Question

create table #test(id int,quarter int,profile int,memo varchar(20)) 
insert into #test values(1,1,1000,'jd')
insert into #test values(1,2,2000,'as')
insert into #test values(1,3,4000,'sdd')
insert into #test values(1,4,5000,'sfsf')
insert into #test values(2,1,3000,'ddd')
insert into #test values(2,2,3500,'veve')
insert into #test values(2,3,4200,'eee')
insert into #test values(2,4,5500,'sss')我想转成这样
id  A B  C  D  memo
1   1000  2000 4000 5000 jdassdsfsf
2 3000 3500 4200 5500 dddveveeeesss如何弄。谢谢了

Answer 1

你这个是字符串的合并
合并列值 
--*******************************************************************************************
表结构，数据如下： 
id    value 
----- ------ 
1    aa 
1    bb 
2    aaa 
2    bbb 
2    ccc 需要得到结果： 
id    values 
------ ----------- 
1      aa,bb 
2      aaa,bbb,ccc 
即：group by id, 求 value 的和（字符串相加） 1. 旧的解决方法(在sql server 2000中只能用函数解决。) 
--=============================================================================
create table tb(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') 
go 
--1. 创建处理函数 
CREATE FUNCTION dbo.f_strUnite(@id int) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ',' + value FROM tb WHERE id=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
-- 调用函数 
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id 
drop table tb 
drop function dbo.f_strUnite 
go
/* 
id          value      
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
（所影响的行数为 2 行） 
*/ 
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) 
create table tb(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') 
go 
-- 查询处理 
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, '') 
)N 
drop table tb /* 
id          values 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc (2 行受影响) 
*/ --SQL2005中的方法2 
create table tb(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') 
go select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') 
from tb 
group by id /* 
id          values 
----------- -------------------- 
1          aa,bb 
2          aaa,bbb,ccc (2 row(s) affected) */ 

Answer 2

select id,[1] as A,[2] AS B,[3] AS C,[4] AS D,memo 
from 
(
select ID,quarter,profile
,(select ''+memo  from #test where a.id=id for xml path(''))  as memo 
from #test a
) a pivot
(SUM(profile) for quarter in([1],[2],[3],[4])) as pvt
ORDER BY ID          id           A           B           C           D memo
----------- ----------- ----------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1        1000        2000        4000        5000 jdassddsfsf
          2        3000        3500        4200        5500 dddveveeeesss(2 行受影响)

Answer 3

create table test(id int,quarter int,profile int,memo varchar(20)) 
insert into test values(1,1,1000,'jd')
insert into test values(1,2,2000,'as')
insert into test values(1,3,4000,'sdd')
insert into test values(1,4,5000,'sfsf')
insert into test values(2,1,3000,'ddd')
insert into test values(2,2,3500,'veve')
insert into test values(2,3,4200,'eee')
insert into test values(2,4,5500,'sss')
gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str +  cast(memo as varchar) from test where id = @id
    --set @str = right(@str , len(@str) - 1)
    return @str
end
go--调用函数
select id , 
       max(case quarter when 1 then profile else 0 end) [1],
       max(case quarter when 2 then profile else 0 end) [2],
       max(case quarter when 3 then profile else 0 end) [3],
       max(case quarter when 4 then profile else 0 end) [4],
      memo = dbo.f_str(id) 
from test group by iddrop function dbo.f_str
drop table test/*
id          1           2           3           4           memo         
----------- ----------- ----------- ----------- ----------- -------------
1           1000        2000        4000        5000        jdassddsfsf
2           3000        3500        4200        5500        dddveveeeesss（所影响的行数为 2 行）
*/

Answer 4

create table #test(id int,quarter int,profile int,memo varchar(20)) 
insert into #test values(1,1,1000,'jd')
insert into #test values(1,2,2000,'as')
insert into #test values(1,3,4000,'sdd')
insert into #test values(1,4,5000,'sfsf')
insert into #test values(2,1,3000,'ddd')
insert into #test values(2,2,3500,'veve')
insert into #test values(2,3,4200,'eee')
insert into #test values(2,4,5500,'sss')select id,
 max(case quarter when 1 then profile end )as A,
 max(case quarter when 2 then profile end )as B,
 max(case quarter when 3 then profile end )as C,
 max(case quarter when 4 then profile end )as D,
 max(case quarter when 1 then memo end)+
max(case quarter when 2 then memo end)+
max(case quarter when 3 then memo end)+
max(case quarter when 4 then memo end) as memo 
from
#test
group by id
id          A           B           C           D           memo                                                                             
----------- ----------- ----------- ----------- ----------- -------------------------------------------------------------------------------- 
1           1000        2000        4000        5000        jdassddsfsf
2           3000        3500        4200        5500        dddveveeeesss（所影响的行数为 2 行）警告: 聚合或其它 SET 操作消除了空值。

Answer 5

不好意思，这个需求要变动一下。
create table #test(id int,quarter int,profile int,memo varchar(20)) 
insert into #test values(1,1,1000,'jd')
insert into #test values(1,1,2000,'as')
insert into #test values(1,3,4000,'sdd')
insert into #test values(1,4,5000,'sfsf')
insert into #test values(2,1,3000,'ddd')
insert into #test values(2,2,3500,'veve')
insert into #test values(2,3,4200,'eee')
insert into #test values(2,4,5500,'sss')我想转成这样 quarter一样的时候memo要合成一个
id  A  B  C  D  memo1 memo2 memo3 memo4
1  1000  2000  4000  5000  jdas sdd sfsf
2  3000 3 500  4200  5500  ddd veve eee sss
大家再帮我想想办法，一会儿再加点分。谢谢了

Answer 6

create table test(id int,quarter int,profile int,memo varchar(20)) 
insert into test values(1,1,1000,'jd')
insert into test values(1,2,2000,'as')
insert into test values(1,3,4000,'sdd')
insert into test values(1,4,5000,'sfsf')
insert into test values(2,1,3000,'ddd')
insert into test values(2,2,3500,'veve')
insert into test values(2,3,4200,'eee')
insert into test values(2,4,5500,'sss')
go--调用函数
select id , 
       max(case quarter when 1 then profile else 0 end) [A],
       max(case quarter when 2 then profile else 0 end) [B],
       max(case quarter when 3 then profile else 0 end) [C],
       max(case quarter when 4 then profile else 0 end) [D],
       max(case quarter when 1 then memo else '' end) [memo1],
       max(case quarter when 2 then memo else '' end) [memo2],
       max(case quarter when 3 then memo else '' end) [memo3],
       max(case quarter when 4 then memo else '' end) [memo4]
from test group by iddrop table test/*
id          A           B           C           D           memo1                memo2                memo3                memo4                
----------- ----------- ----------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- 
1           1000        2000        4000        5000        jd                   as                   sdd                  sfsf
2           3000        3500        4200        5500        ddd                  veve                 eee                  sss（所影响的行数为 2 行）*/

Answer 7

select a.id,A,B,C,D,memo1,memo2,memo3,memo4 from (
select id,[1] as A,[2] AS B,[3] AS C,[4] AS D
from (select id ,quarter ,profile from test)a
pivot
(SUM(profile) for quarter in([1],[2],[3],[4])) as pvt
) a,
(
select id,[1] as memo1,[2] AS memo2,[3] AS memo3,[4] AS memo4
from (select id ,quarter ,memo from test)a
pivot
(max(memo) for quarter in([1],[2],[3],[4])) as pvt
) b
WHERE A.ID=B.ID         id           A           B           C           D memo1                memo2                memo3                memo4
----------- ----------- ----------- ----------- ----------- -------------------- -------------------- -------------------- --------------------
          1        1000        2000        4000        5000 jd                   as                   sdd                  sfsf
          2        3000        3500        4200        5500 ddd                  veve                 eee                  sss(2 行受影响)

Answer 8

不好意思，这个需求要变动一下。
create table #test(id int,quarter int,profile int,memo varchar(20)) 
insert into #test values(1,1,1000,'jd')
insert into #test values(1,1,2000,'as')
insert into #test values(1,3,4000,'sdd')
insert into #test values(1,4,5000,'sfsf')
insert into #test values(2,1,3000,'ddd')
insert into #test values(2,2,3500,'veve')
insert into #test values(2,3,4200,'eee')
insert into #test values(2,4,5500,'sss')我想转成这样 quarter一样的时候memo要合成一个
id A B C D memo1 memo2 memo3 memo4
1 1000 2000 4000 5000 jdas sdd sfsf
2 3000 3 500 4200 5500 ddd veve eee sss第一条数据memo1 的值为jdas| memo2值等于sdd   | memo3值等于sfsf | memo4值为空
第二条数据memo1值 为ddd |memo2值为veve| memo3值为eee ,meme4值为sss
这里写不了表格所以错位了。<table></table>
大家再帮我想想办法，一会儿再加点分。谢谢了