队列 转换 合并 分拆

–方法1.使用xml完成
SELECT A.id, B.value FROM
(
SELECT id, [value] = CONVERT(xml,'<root><v>’ +
REPLACE([value], ‘,’, ‘</v><v>’) +
‘</v></root>’) FROM tb
) A OUTER APPLY
(
SELECT value = N.v.value(‘.’, ‘varchar(100)’) FROM
A.[value].nodes(‘/root/v’) N(v)
) B

  • value FROM tb
    WHERE id=@id
    RETU昂科雷N 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
    二零零七中用OUTE兰德GL450 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 行受影响) */
    –SQL二〇〇七中的方法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) */ drop
    table tb /*
    标题:分拆列值1
    作者:爱新觉罗.毓华(十八年风霜,守得大将传奇冰山雪六月春开)
    时间:2010-11-20 地点:吉林尼科西亚 描述 有表tb, 如下: id value ———–
    ———– 1 aa,bb 2 aaa,bbb,ccc 欲按id,分拆value列, 分拆后结果如下: id
    value ———– ——– 1 aa 1 bb 2 aaa 2 bbb 2 ccc */
    –1. 旧的消除方法(sql server 3000) SELECT
    TOP 7000 id =
    IDENTITY(int,
    1, 1) INTO #
    FROM syscolumns a, syscolumns b SELECT
    A.id, value = SUBSTRING(A.[value], B.id,
    CHARINDEX(‘,’,
    A.[value]
  • ‘,’, B.id)
  • B.id) FROM tb A,
    # B WHERE SUBSTRING(‘,’
  • A.[value], B.id,
    1) =
    ‘,’
    DROP TABLE #
    –2. 新永生的消除办法(sql server 2007)
    create
    table tb(id int,value
    varchar(30)) insert
    into tb values(1,’aa,bb’)
    insert into tb
    values(2,’aaa,bbb,ccc’)
    go SELECT
    A.id, B.value FROM( SELECT id,
    [value]
    = CONVERT(xml,'<root><v>’
  • REPLACE([value],
    ‘,’,
    ‘</v><v>’)
  • ‘</v></root>’)
    FROM tb )A OUTER
    APPLY( SELECT value =
    N.v.value(‘.’,
    ‘varchar(100)’)
    FROM A.[value].nodes(‘/root/v’) N(v) )B
    DROP TABLE tb
    /* id value ———–
    —————————— 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响)
    */

难题:倘诺有张学生战表表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想成为(获得如下结果):
姓名 语文 数学 物理

 1 –分拆列值 
 2 –原著:邹建 
 3 –改编:爱新觉罗.毓华(十八年风雨,守得冰山雪水芝开) 2006-12-16 青海布里斯班
 4 
 5 –有表tb, 如下: 
 6 –id value 
 7 ————- ———– 
 8 –1 aa,bb 
 9 –2 aaa,bbb,ccc 
10 –欲按id,分拆value列, 分拆后结果如下: 
11 –id value 
12 ————- ——– 
13 –1 aa 
14 –1 bb 
15 –2 aaa 
16 –2 bbb 
17 –2 ccc 
18 
19 –1. 旧的缓解方法(sql server 三千) 
20 SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
21 
22 SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(‘,’, A.[values] + ‘,’, B.id) – B.id) 
23 FROM tb A, # B 
24 WHERE SUBSTRING(‘,’ + A.[values], B.id, 1) = ‘,’
25 
26 DROP TABLE #
27 
28 –2. 新的缓解格局(sql server 二〇〇六) 
29 create table tb(id int,value varchar(30)) 
30 insert into tb values(1,’aa,bb’) 
31 insert into tb values(2,’aaa,bbb,ccc’) 
32 go 
33 SELECT A.id, B.value 
34 FROM( 
35 SELECT id, [value] = CONVERT(xml,’ <root> <v>’ + REPLACE([value], ‘,’, ‘ </v> <v>’) + ‘ </v> </root>’) FROM tb 
36 )A 
37 OUTER APPLY( 
38 SELECT value = N.v.value(‘.’, ‘varchar(100)’) FROM A.[value].nodes(‘/root/v’) N(v) 
39 )B
40 
41 DROP TABLE tb
42 
43 /* 
44 id value 
45 ———– —————————— 
46 1 aa 
47 1 bb 
48 2 aaa 
49 2 bbb 
50 2 ccc
51 
52 (5 行受影响)
53 */


SQL code

张三 总分   250.00

*/

select*from
(
select 姓名 as 姓名 ,
课程 =’语文’ , 分数
= 语文 from tb
unionall
select 姓名 as 姓名 ,
课程 =’数学’ , 分数
= 数学 from tb
unionall
select 姓名 as 姓名 ,
课程 =’物理’ , 分数
= 物理 from tb
unionall
select 姓名 as 姓名 ,
课程 =’平均分’ , 分数
=cast((语文

  • 数学 +
    物理)*1.0/3asdecimal(18,2)) from tb
    unionall
    select 姓名 as 姓名 ,
    课程 =’总分’ , 分数
    = 语文 + 数学
  • 物理 from tb
    ) t
    orderby 姓名 ,
    case 课程 when’语文’then1when’数学’then2when’物理’then3when’平均分’then4when’总分’then5end

droptable tb

 

 

 

/*表结构,数据如下: 
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

    两千中不得不用函数消除。) 

    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 2006中用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

                    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 行受影响) 
*/ 

–SQL二〇〇五中的方法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  
*/ 

drop table tb 

 

 

 

 

/*有表tb,
如下: 
id          value 
———– ———– 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
———– ——– 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc */

  1. 旧的缓解办法(sql server 两千)  
    select a.id, substring(a.[value],
    b.number, charindex(‘,’,
    a.[value] + ‘,’,
    b.number) –
    b.number) 
    FROM tb a, master..spt_values  b 
    WHERE b.type=’p’ and substring(‘,’ +
    a.[value],b.number,
    1) = ‘,’ 

  2. 新的消除方法(sql server 二零零七) 
    create table tb(id
    int,value varchar(30)) 
    insert into tb
    values(1,’aa,bb’) 
    insert into tb
    values(2,’aaa,bbb,ccc’) 
    go 
    SELECT a.id, b.value 
    FROM( 
        SELECT id, [value] = CONVERT(xml,’
    <root> <v>’ + REPLaCE([value],
    ‘,’,
    ‘ </v> <v>’)
    + ‘
    </v> </root>’)
    FROM tb 
    )a 
    OUTER aPPLY( 
        SELECT value =
    N.v.value(‘.’,
    ‘varchar(100)’)
    FROM a.[value].nodes(‘/root/v’) N(v) 
    )b 

DROP TabLE tb 

/* 
id          value 
———– —————————— 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 

(5 行受影响) 
*/


联合列值 –******************************************************************************************* 表结构,数据如下: 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 的谷青阳和(字符串相加)

/* 2 */
select DATEPART(hh, CreateTime) 时间,count(*) 总量 into #tb  from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime)

–方法2.使用CTE完成
;with tt as
(select id,[value]=cast(left([value],charindex(‘,’,[value]+’,’)-1)
as
nvarchar(100)),Split=cast(stuff([value]+’,’,1,charindex(‘,’,[value]+’,’),”)
as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(‘,’,Split)-1) as
nvarchar(100)),Split= cast(stuff(Split,1,charindex(‘,’,Split),”) as
nvarchar(100)) from tt where split>”
)
select id,[value] from tt order by id option (MAXRECURSION 0)

  1. 旧的化解方法(在sql server
    两千中只好用函数化解。) –============================================================================= 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 +
    ‘,’

 
/*
时间    9    10    11    12    13    14    15    16    17    18
总量    187    369    340    56    374    381    307    410    516    73
*/

有表tb, 如下:
id value


发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图