SQL一列转换成一行！

一列转换成一行！

表1内容为
    cright
    |2|5|6|7|
    |1|2|3|4|5|6|7|
    |5|6|7|8|

    想得到表2

    r1                                   r2                                               r3
    |2|5|6|7|                   |1|2|3|4|5|6|7|                     |5|6|7|8|

    请问如何转换?

create     table     A
    (
        cright     varchar(20)
    )
    insert     A
    select     ‘|2|5|6|7|’     union
    select     ‘|1|2|3|4|5|6|7|’     union
    select     ‘|5|6|7|8|’

    –查询
    declare     @str     varchar(8000),@sql     varchar(8000),@i     int
    select     @str=isnull(@str,”)+’,”’+cright+””     from     A
    select     @sql=”,@i=1
    while     @i<=(select     count(1)     from     A)
    begin
              select     @sql=@sql+’,[r'+convert(varchar,@i)+']     varchar(20)’
                            ,@i=@i+1
    end
    select     @sql=’     create     table     #(’+stuff(@sql,1,1,”)+’)'+
                            ‘     insert     #     select     ‘+stuff(@str,1,1,”)+
                            ‘     select     *     from     #’+
                            ‘     drop     table     #’
    exec(@sql)

    –删除测试环境
    drop     table     A

    –结果
    /*
    r1                                         r2                                         r3
    ——————–     ——————–     ——————–
    |1|2|3|4|5|6|7|               |2|5|6|7|                           |5|6|7|8|

    （所影响的行数为     1     行）
    */

来源：bqb的专栏

收藏本文到网摘：

Random Posts

This entry was posted on 星期四, 10月 4th, 2007 at 6:40 pm and is filed under 数据库|SQLServer. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.