SQL查询将多个行合并为单行 [英] SQL Query to Concatinate multuple rows into single row

问题描述

我有一个orale查询，以返回3个表的行.
结果集如下所示

I have an orale query to return rows from 3 table''s.
The result set looks like following

table1 CardNO
table2 JobCode
table3 User

CardNO   JobCode   User
1072      CarWash   A
1072      CarWash   B

我想与用户A和B关联并返回类似
的结果

I want to Concatinate user A and B and return a result like

CardNo    JobCode   User
1072       Carwash   A,B

我如何使用oracleSQL实现它.希望您能对我有所帮助.

How can i achieve it by using oracleSQL. Hope u can help me.

推荐答案

在SQL Server中，应使用"Stuff".

in SQL Server, you should use ''Stuff''.

Select  CardNO As CardNo,
    JobCode As JobCode,
    Stuff(
    (
        Select  ', ' + User
            From    table3
            For Xml Path('')
    ), 1, 2, N'')   As User
    From    table1, table2

由于我不知道您的表之间的关系，因此该查询将返回两个相同的行.在材料"内部的选择"中使用位置"来过滤数据.

在Oracle中，您应该使用Replace.
有关Oracle字符串隐藏的信息，请参见:http://asktom.oracle.com/pls/asktom/f?p = 100:11:0 ::::: P11_QUESTION_ID:2196162600402
--------------------
问候

H.Maadani

since I dont know the relations between your tables, this query is going to return two same rows. use ''where'' in ''select'' inside ''stuff'' to filter data.

in Oracle you shoul use Replace.
for Oracle string concating, see this :http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
--------------------
Regards

H.Maadani

这篇关于SQL查询将多个行合并为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！