Geek Answers Handbook

Select multiple rows from SQL to one row in a subquery

I have a 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 this COALESCE function, I want to get the entire type of payment for the selected order - for example, orderID 123 has payTypes = Card, Cash, orderID have payTypes = Cash.

The problem is that I want to have it on one line as the last line of the main request, for example: orderID, Document.number, UserInfo.name + surname, dateStart, dateEnd, → card, cash <- but after trying the request, like above, I got the 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 payment types in a subquery and return them as one line?

+4
source share
3 answers

Based on the syntax you used, I assume that you are using SQL-Server, and therefore you can use the SQL Server extension to expand the rows.

 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 replaced all of your ANSI 89 connections with ANSI 92, as this is a more modern syntax and is generally accepted as the more (I claim it is generally accepted as it is, of course, a personal preference, and there are still some cases where Oracle optimizes ANSI89 connections are better).

EDIT

After viewing your duplicate data from the Product_Document table, you can delete it using the following:

 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;
+5
source

This SO article / answer discusses well different approaches to folding / aggregating a series of varchar rows into a single column, for example, you say fooobar.com/questions/738009 / ...

0
source

Yes. But it depends on the DBMS used.

To create a list of data in one line, you must use an analytical function specific to the DBMS used, or write your own specific function.

In oracle: the WM_CONCAT, LISTAGG, or XMLAGG functions will turn multiple strings that share similar data into a list.

Coalese only works one line at a time and does not concatenate lines.

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

Usage / Syntax is changing. therefore, I suggest looking under these conditions, depending on your RDBMS.

0
source

More articles:


All Articles