0

I have table with field that contains coma separated string. I whant create query thats return follow result:

I want to create query that return split text. For exapmle:

Data in table:

| ID                                   | Names                            |
| ------------------------------------ | -------------------------------- |
| 63F5D993-3AC9-4EEA-8007-B669542BAD9A | John Smith,Kerry King,Tom Arraya |

Required result:

 ID                                   | Names                             
------------------------------------  | -----------
63F5D993-3AC9-4EEA-8007-B669542BAD9A  | John Smith                       
------------------------------------- | -----------
63F5D993-3AC9-4EEA-8007-B669542BAD9A  | Kerry King                       
------------------------------------- | -----------
63F5D993-3AC9-4EEA-8007-B669542BAD9A  | Tom Arraya

I found "split" function for T-SQL but its works not quite right for my case. I can't execute it like this:

SELECT dbo.Split(dbo.name, ',') FROM dbo.Mytable

It can execute only follows:

SELECT * FROM dbo.Split('John Smith,Kerry King,Tom Arraya', ',')

But it does not suit me. Also i attempted write cursor:

DECLARE @bkb varchar(256)
DECLARE @Bkb_Cursor CURSOR 
SET @Bkb_Cursor = CURSOR SCROLL FOR  
SELECT bkb.best_know_by
  FROM  [sugarcrm_cmsru_dev].[dbo].[contacts] c
  left JOIN [dbo].[email_addr_bean_rel]  eb
  ON eb.[bean_id] = c.[id]
  JOIN [dbo].[email_addresses] ea
  ON ea.[id] = eb.[email_address_id]
  JOIN [dbo].[contacts_cstm] ccs
  ON eb.bean_id = ccs.id_c
  left JOIN [dbo].[BestKnowBy$] bkb
  ON c.[campaign_id] =bkb.Con_id
  where c.deleted = 0;  
OPEN @Bkb_Cursor;  
FETCH NEXT FROM @Bkb_Cursor
INTO @bkb;
WHILE @@FETCH_STATUS = 0
BEGIN  
      PRINT dbo.Splitfn(@bkb);
      FETCH NEXT FROM @Bkb_Cursor INTO @bkb;  
END
CLOSE @Bkb_Cursor;  
DEALLOCATE @Bkb_Cursor;  
GO

But it is not worked. I get error "Column "dbo" is not allowed in this context, and the user-defined function or aggregate "dbo.Splitfn" could not be found."

How can I solve this problem?

My query looks like follow:

SELECT c.[id], 
       bkb.best_know_by
  FROM  [sugarcrm_cmsru_dev].[dbo].[contacts] c
  left JOIN [dbo].[email_addr_bean_rel]  eb
  ON eb.[bean_id] = c.[id]
  JOIN [dbo].[email_addresses] ea
  ON ea.[id] = eb.[email_address_id]
  JOIN [dbo].[contacts_cstm] ccs
  ON eb.bean_id = ccs.id_c
  left JOIN [dbo].[BestKnowBy$] bkb
  ON c.[campaign_id] =bkb.Con_id
  where c.deleted = 0;

The bkb.best_know_by field contains comma separated string. How can i use "Cross Apply" in this case?

Improve this question
1
  • I think the joins in your query are wrong, because the INNER JOIN between ea and eb implies an INNER JOIN between c and eb. Since you've used a LEFT OUTER JOIN to bkb, you may want to use OUTER APPLY instead of CROSS APPLY. OUTER APPLY behaves like a LEFT OUTER JOIN, meaning that rows will be returned even if the function returns no rows itself (the columns returned by the function will be output as NULL). A CROSS APPLY implies an INNER JOIN, so rows will only be returned if there is a corresponding row from the function. Commented Oct 4, 2016 at 13:10

2 Answers 2

Reset to default
3

You need to use CROSS APPLY in combination with your table's result set which will execute the function for every row:

SELECT st.ID, spl.value
FROM SplitTest st
CROSS APPLY string_split(st.Names, ',') spl

results

Edit: With regard to the addition of your query to your question, you could do the following:

;WITH CTE_Query AS (
    SELECT c.[id], 
       bkb.best_know_by
    FROM  [sugarcrm_cmsru_dev].[dbo].[contacts] c
    left JOIN [dbo].[email_addr_bean_rel]  eb
    ON eb.[bean_id] = c.[id]
    JOIN [dbo].[email_addresses] ea
    ON ea.[id] = eb.[email_address_id]
    JOIN [dbo].[contacts_cstm] ccs
    ON eb.bean_id = ccs.id_c
    left JOIN [dbo].[BestKnowBy$] bkb
    ON c.[campaign_id] =bkb.Con_id
    where c.deleted = 0
)
SELECT cte.id, spl.value
FROM CTE_Query AS cte
CROSS APPLY string_split(cte.best_know_by, ',') spl
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I added details to my question. Please look. Thanks.
Without picking apart your table structure, I suggest you wrap the whole thing in a CTE/subquery then perform the CROSS APPLY. See edit.
1

Cross Apply will do the trick

Select A.ID
      ,Names = B.Item   -- << Return Field from your Split Function
 From  YourTable A
 Cross Apply (Select * from dbo.Split(A.Names, ',') ) B

With your query

SELECT c.[id]
      ,S.*      --<< Removed bkb.best_know_by and Replaced with S.* (don't know your Split() Return Field)
 FROM  [sugarcrm_cmsru_dev].[dbo].[contacts] c
 LEFT JOIN [dbo].[email_addr_bean_rel]  eb ON eb.[bean_id] = c.[id]
 JOIN [dbo].[email_addresses] ea ON ea.[id] = eb.[email_address_id]
 JOIN [dbo].[contacts_cstm] ccs   ON eb.bean_id = ccs.id_c
 LEFT JOIN [dbo].[BestKnowBy$] bkb   ON c.[campaign_id] =bkb.Con_id
 Cross Apply dbo.Split(bkb.best_know_by,',')  S
 where c.deleted = 0;
Improve this answer

7 Comments

You don't need to wrap the split function in a subquery - Cross Apply dbo.Split(A.Names, ',') B will produce the same output as Cross Apply (Select * from dbo.Split(A.Names, ',') ) B
@knuckles Yes you are correct.. Chalk this up to OCD and old habits. There is no performance hit, and if you want to select only the 1st and 3rd items (for example) it would have to be wrapped
I added details to my question. Please look. Thanks.
@Seva Updated answer see second part
Another observation (I'm not out to get you, honest) - the CROSS APPLY statement effectively implies an INNER JOIN. To preserve the LEFT OUTER JOIN behaviour of the join above, you would use OUTER APPLY instead. TBH, I think the joins in the original question are mixed up already, so I'm not sure what the OP wants.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.