×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

再来一个SQL问题探讨 - Concatenation. Thanks for your input.

本文发表在 rolia.net 枫下论坛再来一个SQL问题探讨

以下这个问题当然可以用循环的方式来解决,但是否有好的方法,比如,一个或几个SQL语句来解决?
目的是想不使用Loop来解决这个问题
You should give one or a few SQLs then the result will concatenate all the coloumn C2 according to C1 value.
(You allow to create/use one or more temporary tables if you believe it will give efficiency.)
You can give specific solution to Oracle, MS-SQL or DB2, etc. as long as it is a good solution to that system.

初始条件:
Sample Table T1
----------------------------------------------------
Id C1 C2
-------------------------------
1 101 AA
2 101 BBBB
3 101 HHHH
4 102 CCC
5 102 KKKKKK
6 105 ZZ
. . .
. . .
. . .
. . .


(Note: You could have multiple rows repeat for the same C1 value)
(Note: Id is a consistent integer number)


Result Set
Table T2
-----------------------------------------------------
NewId NewC1 NewC2
1 101 AABBBBHHHH
2 102 CCCKKKKKK
3 105 ZZ
. . .
. . .


Thanks for input.
:)更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 再来一个SQL问题探讨 - Concatenation. Thanks for your input.
    本文发表在 rolia.net 枫下论坛再来一个SQL问题探讨

    以下这个问题当然可以用循环的方式来解决,但是否有好的方法,比如,一个或几个SQL语句来解决?
    目的是想不使用Loop来解决这个问题
    You should give one or a few SQLs then the result will concatenate all the coloumn C2 according to C1 value.
    (You allow to create/use one or more temporary tables if you believe it will give efficiency.)
    You can give specific solution to Oracle, MS-SQL or DB2, etc. as long as it is a good solution to that system.

    初始条件:
    Sample Table T1
    ----------------------------------------------------
    Id C1 C2
    -------------------------------
    1 101 AA
    2 101 BBBB
    3 101 HHHH
    4 102 CCC
    5 102 KKKKKK
    6 105 ZZ
    . . .
    . . .
    . . .
    . . .


    (Note: You could have multiple rows repeat for the same C1 value)
    (Note: Id is a consistent integer number)


    Result Set
    Table T2
    -----------------------------------------------------
    NewId NewC1 NewC2
    1 101 AABBBBHHHH
    2 102 CCCKKKKKK
    3 105 ZZ
    . . .
    . . .


    Thanks for input.
    :)更多精彩文章及讨论,请光临枫下论坛 rolia.net
    • 啊?这问题不是刚问过吗?ORACLE解决方案:到asktom.oracle.com搜索一下stragg
    • Try UDF?
    • One SQL Query in MySQL
      Select count(1) as NewId, a.C1 as NewC1, a.NewC2
      from (select c.C1, group_concat(c.C2 ORDER BY c.Id SEPARATOR '') as NewC2 from T1 c group by c.C1) as a, (select distinct d.C1 from T1 d) as b
      where a.C1 >= b.C1
      group by a.C1
      order by NewId