拼串sql怎么着写

分拆value列,id value,—— 1 aa 1 bb 2 aaa 2 bbb 2 ccc 需要得到结果,id value —–,广东深圳, 3 –改编, 2 –原著


集合列值 –******************************************************************************************* 表结构,数据如下: id value —–
—— 一 aa 一 bb 二 aaa 2 bbb 2 ccc 要求得到结果: id values
—— ———– 一 aa,bb 二 aaa,bbb,ccc 即:group
by id, 求 value 的谷青阳和(字符串相加)

/*
标题:普通行列转换(version 二.0)
小编:爱新觉罗.毓华(拾八年风雨,守得冰山雪芙蓉开)
时间:2008-03-09
地点:湖南尼科西亚
表明:普通行列转换(version 一.0)仅针对sql server
三千提供静态和动态写法,version 2.0充实sql server 200五的关于写法。

 1 –分拆列值 
 2 –原著:邹建 
 三 –改编:爱新觉罗.毓华(拾八年风雨,守得冰山雪泽芝开) 二零零五-1二-1陆 湖北布拉迪斯拉发
 4 
 5 –有表tb, 如下: 
 6 –id value 
 7 ————- ———– 
 8 –1 aa,bb 
 9 –2 aaa,bbb,ccc 
十 –欲按id,分拆value列, 分拆后结果如下: 
11 –id value 
12 ————- ——– 
13 –1 aa 
14 –1 bb 
15 –2 aaa 
16 –2 bbb 
17 –2 ccc 
18 
1玖 –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 –二. 新的化解方法(sql server 200伍) 
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 (伍 行受影响)
53 */

–方法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
    2000中不得不用函数化解。) –============================================================================= create
    table tb(id int, value
    varchar(10)) insert
    into tb values(1, ‘aa’)
    insert into tb
    values(一, ‘bb’)
    insert into tb
    values(二, ‘aaa’)
    insert into tb
    values(二, ‘bbb’)
    insert into tb
    values(二, ‘ccc’)
    go –一.
    创设处理函数 CREATE
    FUNCTION dbo.f_strUnite(@id
    int) RETURNS
    varchar(8000) AS
    BEGIN DECLARE
    @str varchar(8000) SET
    @str =
    ” SELECT
    @str =
    @str +
    ‘,’

李四 74   84   94

–方法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,
    一, 壹, ”)
    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 贰 aaa,bbb,ccc (所影响的行数为 2行) */ –=================================================================================== 2. 新绿业电脑高校的消除办法(在sql server
    二〇〇五中用OUTEKoleos APPLY等化解。) create
    table tb(id int, value
    varchar(十)) insert
    into tb values(一, ‘aa’)
    insert into tb
    values(一, ‘bb’)
    insert into tb
    values(2, ‘aaa’)
    insert into tb
    values(贰, ‘bbb’)
    insert into tb
    values(二, ‘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 2aaa,bbb,ccc (2 行受影响) */
    –SQL200第55中学的方法2 create
    table tb(id int, value
    varchar(拾)) insert
    into tb values(1, ‘aa’)
    insert into tb
    values(一, ‘bb’)
    insert into tb
    values(贰, ‘aaa’)
    insert into tb
    values(二, ‘bbb’)
    insert into tb
    values(二, ‘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 /*
    标题:分拆列值壹小编:爱新觉罗.毓华(拾8年风霜,守得老将神话冰山雪水水花开)
    时间:二〇一〇-1一-20 地方:江苏柏林(Berlin) 描述 有表tb, 如下: id value ———–
    ———– 一 aa,bb 2 aaa,bbb,ccc 欲按id,分拆value列, 分拆后结果如下: id
    value ———– ——– 一 aa 一 bb 2 aaa 二 bbb 二 ccc */
    –一. 旧的化解方法(sql server 三千) SELECT
    TOP 八千 id =
    IDENTITY(int,
    一, 一) 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 200伍)
    create
    table tb(id int,value
    varchar(30)) insert
    into tb values(一,’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 ———–
    —————————— 一 aa 一 bb 贰 aaa 2 bbb 二 ccc (5 行受影响)
    */

set @sql = @sql + ‘ from (select DATEPART(hh, CreateTime) 时间,count(*) 总量   from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
 group by DATEPART(hh, CreateTime) 
)  t ‘
exec(@sql) 

DROP TABLE tb

SQL code


有表tb, 如下:
id value

张三 总分   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 
    –一. 成立处理函数
    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 二〇〇五中用OUTER应用程式LY等消除。) 
    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 

(二 行受影响) 
*/ 

–SQL2006中的方法二
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 3000)  
    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 2005) 
    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 

(伍 行受影响) 
*/