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?
Best Answer
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.
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: