CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Shivang Kaul
Shivang Kaul

Reputation: 53

SQL Server: Subquery returned more than 1 value

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

Upvotes: 4

Views: 1915

Answers (3)

Shivang Kaul
Shivang Kaul

Reputation: 53

I have already founded a work around solution, but thanks a ton for your reply.. The workaround I founded is DECLARE @SKUID Varchar(MAX)

DECLARE @mytemp Table(mykey INT IDENTITY(1,1),OrderSummary Varchar(MAX))

DECLARE @mykey INT

INSERT INTO @mytemp(OrderSummary) SELECT OrderSummary FROM SAM_STORE_OM_Orders

SET @SKUID = ''

select @mykey = min( mykey ) from @mytemp

while @mykey is not null begin select @SKUID = @SKUID + OrderSummary from @mytemp where mykey = @mykey select @mykey = min( mykey ) from @mytemp where mykey > @mykey end

SELECT SUBSTRING(T.Value,1,CHARINDEX(',',T.Value)-1) FROM ( Select * FROM Split('|',@SKUID) )T

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try:

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

Upvotes: 2

Related Questions