Select multiple rows from SQL into one row in subquery

Question

I have query like:

DECLARE @razem VARCHAR(MAX);

SELECT  Ordering.orderID , 
        Document.number, 
        (User_info.name  +' '+ User_info.surname),   
        Ordering.dateStart, 
        Ordering.dateEnd ,   
        (
            select   COALESCE(' ',@razem)+sell_type.name as r  
            from    Ordering_sell_type, Sell_type 
            where   orderID = Ordering.orderID and 
                    Ordering_sell_type.sell_typeID = sell_type.sell_typeID
        ) podz
FROM    Ordering, User_info, Product_Document, Document, Document_type   
WHERE   Ordering.orderID = Product_document.orderID  
        AND Document.documentID = Document_type.documentID  
        AND Document.documentID = Product_document.documentID  
        AND  Ordering.userID = User_info.userID   
        AND Ordering.isClosed = 1 AND Document_type.typeID = 1   
GROUP   BY  Document.isitfiscal, Document.refDocID, 
            Document.number, Ordering.orderID, User_info.name, 
            User_info.surname, Ordering.dateStart, 
            Ordering.dateEnd , Ordering.isCLosed  
ORDER   BY Ordering.dateEnd

And in that COALESCE function I want to get all pay type for selected order - for example, orderID 123 have payTypes = Card, Cash, orderID have payTypes = Cash.

Problem is that I want to have it in one simply row as last row of main query, like: orderID, Document.number, UserInfo.name+surname, dateStart, dateEnd, ->card, cash<- but after trying query like above i got error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

because it returns more than one row. Is it possible, to get pay types in subquery and return as one string?

In oracle: WM_CONCAT, LISTAGG or XMLAGG functions will turn multiple rows sharing similar data into a list. Coalese only works one row at a time and doesn't combine rows. [XMLpath](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) in SQL server i believe does similar combinations; I think mySQL uses [Group_Concat](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) — xQbert, Feb 26 '13 at 13:55

score 5 · Answer 1 · edited Jul 29 '20 at 18:25

Based on the syntax you have used, I am assuming you are using SQL-Server, and as such you can use SQL-Servers XML extension to concatenate strings.

SELECT  Ordering.orderID, 
        Document.number, 
        [UserName] = User_info.name  +' '+ User_info.surname,   
        Ordering.dateStart, 
        Ordering.dateEnd,
        [podz] = STUFF((    SELECT  DISTINCT ' ' + SellType.Name
                            FROM    Ordering_Sell_Type
                                    INNER JOIN Sell_Type
                                        ON Sell_Type.sell_typeID = Ordering_Sell_Type.sell_typeID
                            WHERE   Ordering.OrderID = Ordering_SellType.OrderId
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    Ordering
        INNER JOIN User_Info
            ON Ordering.UserID = User_Info.UserID
        INNER JOIN ProductDocument
            ON Ordering.OrderID = Product_Document.OrderID
        INNER JOIN Document
            ON Document.DocumentID = Product_Document.DocumentID
        INNER JOIN Document_Type
            ON Document_Type.DocumentID = Document.DocumentID
WHERE   Ordering.IsClosed = 1
AND     Document_Type.TypeID = 1
ORDER BY Ordering.dateEnd;

Note I have replaced all your ANSI 89 joins with ANSI 92, as this is the more modern syntax, and is generally accepted as the more legible option (I say generally accepted as it is of course personal preference and there are also still some cases when Oracle optimises ANSI89 joins better).

EDIT

Having seen your data the duplicates are from the Product_Document Table, you can remove these by using this:

SELECT  Ordering.orderID, 
        Document.number, 
        [UserName] = User_info.name  +' '+ User_info.surname,   
        Ordering.dateStart, 
        Ordering.dateEnd,
        [podz] = STUFF((    SELECT  DISTINCT ' ' + SellType.Name
                            FROM    Ordering_Sell_Type
                                    INNER JOIN Sell_Type
                                        ON Sell_Type.sell_typeID = Ordering_Sell_Type.sell_typeID
                            WHERE   Ordering.OrderID = Ordering_SellType.OrderId
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    Ordering
        INNER JOIN User_Info
            ON Ordering.UserID = User_Info.UserID
        INNER JOIN 
        (   SELECT  DISTINCT OrderID, DocumentID
            FROM    Product_Document
        ) Product_Document
            ON Ordering.OrderID = Product_Document.OrderID
        INNER JOIN Document
            ON Document.DocumentID = Product_Document.DocumentID
        INNER JOIN Document_Type
            ON Document_Type.DocumentID = Document.DocumentID
WHERE   Ordering.IsClosed = 1
AND     Document_Type.TypeID = 1
ORDER BY Ordering.dateEnd;

If I've not guessed the DBMS correctly there are a number of other methods answered [here](http://stackoverflow.com/questions/10791247/sql-server-possible-pivot-solution/10796492#10796492) — GarethD, Feb 26 '13 at 13:59
Where is the duplication coming from, are there multiple documents per order where the document type is 1? — GarethD, Feb 26 '13 at 14:08
No, there is only one document for each orders. Its like that, orders is only order when its not closed. after we close it (- the order) it becomes document (and it appears in Document table) — user13657, Feb 26 '13 at 14:12
There is nothing really wrong with using DISTINCT on the query, but it is generally better to isolate the source of duplicates and deal with them there. A good way to find them would be to run the same query with `SELECT *` and see where you have varying values in rows where the columns listed above are the same. — GarethD, Feb 26 '13 at 14:15

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

This SO article/answer does a good job of discussing different approaches to rolling-up/aggregating a series of varchar rows into a single column, like you are talking about https://stackoverflow.com/a/2410524/283895

edited May 23 '17 at 12:24

Community

1
1

answered Feb 26 '13 at 13:56

tgolisch

6,549
3
24
42

score 0 · Answer 3 · answered Feb 26 '13 at 13:59

Yes. But it depends on the RDBMS your using.

In order to generate a list of data in one row you must use an analytic function specific to the RDBMS your using, or write your own specific function.

In oracle: WM_CONCAT, LISTAGG or XMLAGG functions will turn multiple rows sharing similar data into a list.

Coalese only works one row at a time and doesn't combine rows.

ORACLE: WM_CONCAT, LISTAGG, XMLAGG
SQL SERVER: XMLpath
My SQL: Group_Concat

Use/Syntax varies. so I suggest searching on those terms depending on your RDBMS.

Select multiple rows from SQL into one row in subquery

3 Answers3