SQL Server: Subquery returned more than 1 value

Question

I have a table Orders which contains a column named OrderSummary. The OrderSummary has data in following format

123,0,0,0,0,0|223,1,1,1,1,1|323,2,2,2,2,2|423,3,3,3,3,3|523,4,4,4,4,4|

Now I wanted to get the first number after | delimiter i.e. I want the output to be this:

123
223
323
423
523

I have a split function which takes delimeter character as first input and string as second input.

I have written the following query but I am getting an error.

SELECT SUBSTRING(T.Value,1,CHARINDEX(',',T.Value)-1) 
 FROM (
    Select * 
    FROM Split('|',(
         SELECT OrderSummary 
         FROM SAM_STORE_OM_Orders 
         GROUP BY OrderSummary)))
    T

The sub-query can return more than one row:

SELECT OrderSummary FROM SAM_STORE_OM_Orders GROUP BY OrderSummary

Answer 1

Please try:

SELECT SUBSTRING(T.Value,1,CHARINDEX(',',T.Value)-1) FROM
    (SELECT VALUE FROM SAM_STORE_OM_Orders CROSS APPLY Split('|', OrderSummary)
)T

Answer 2

Sorry, but I don't use your function;)

;WITH cte (OrderSummaryXML) AS
 (  
  SELECT CAST('<r>' + REPLACE(SUBSTRING(OrderSummary, 1, LEN(OrderSummary)-1), '|', '</r><r>') + '</r>' AS XML)
  FROM SAM_STORE_OM_Orders
  GROUP BY OrderSummary 
  )
SELECT SUBSTRING(OrderSummary, 0, CHARINDEX(',', OrderSummary))
FROM cte
  CROSS APPLY (SELECT Tbl.Col.value('.', 'nvarchar(250)') AS OrderSummary
               FROM OrderSummaryXML.nodes('/r') Tbl(Col)) AS List

Demo on SQLFiddle