Generating rows from a row based on splitting a comma-separated column 1

[TheBugSlayer]

Hello all.
Like often is the case, poor initial design is at the source of the problem that led to this decision.
The problem, there is a billing table with rows that contain more than one CPT Code in the CPT_Code column, separated by comma. A decision has been made to separate the CPT Codes and create a row for each where all the other fields' values would remain the same. So in the the end there will be as many rows as CPT Codes there were in one field originally. A new table will be create with the new data and the old one will be replaced.

So table:

[b]Billing([u]ID[/u], Col1, Col2, CPT_Code)[/b]
001, ABC, 44, '83913,83891,83892'
002, DEF, 39, '83902,83898,83896'

Becomes

[b]Billing(ID, [u]Col1[/u], [u]Col2[/u], [u]CPT_Code[/u])[/b]
001, ABC, 44, '83913'
002, ABC, 44, '83891'
003, ABC, 44, '83892'
004, DEF, 39, '83902'
005, DEF, 39, '83898'
006, DEF, 39, '83896'

Now, I have found a function that splits a column here.
Example

SELECT s from fSplitString('83913,83891,83892,83902,83898,83896', ',')

returns

[b]s[/b]
83913
83891
83892
83902
83898
83896

My problem now is using the function in a query with the billing table. Any way I use it I get an error either saying the column from the billing table that I am passing as an argument to the function is invalid, or that it "cannot find either column "dbo" or the user-defined function or aggregate "dbo.fSplitString", or the name is ambiguous". I am missing something probably...a little rusty lately. Here is how I have been using them so far:

SELECT TOP 10 B.*, F.s
FROM Billing B, dbo.fSplitString(CPT_Code,',') F
 
SELECT B.*, dbo.fSplitString(CPT_Code,',')
FROM Billing B

SELECT *
FROM Billing 
CROSS JOIN dbo.fSplitString(CPT_Code,',')

Any help is appreciated. Keep in mind that this is the design that they retained...Personally I'd further normalize and create a Billing-to-CPT Code table but I lost that battle. Thank you.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

I apologize for not having a lot of time to explain this, but I suggest you do a little research on cross apply and/or outer apply. I think this should take care of the problem.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

George,
No problem. I am also thinking CROSS JOIN...but are you considering the Split String function that I am using, or are you talking about rewriting it myself too? I am working on it...will post my solution.
Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

Ah, stupid! No wonder I wasn't getting anywhere...all this time I am thinking 'CROSS APPLY' yet writing 'CROSS JOIN'...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

This is it:

SELECT B.*, Split.S
FROM Billing B
CROSS APPLY dbo.fSplitString(CPT_Code,',') Split

Thanks George. I had not used CROSS APPLY in so long yet I knew it would be the solution...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

TheBugSlayer,

I am really sorry about giving such a brief answer. I was on the way out the door and thought you would be able to make it work just by suggesting cross apply. You did, and I'm really glad you were able to figure this out.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom