split out comma separated cell into rows 3

[codecomm]

Hello!

We are on SqlServer 2005.

Let me point out at the beginning that I don't have anyway to normalize this structure or get the admins to change the way the data is stored. We don't own the database where this is housed...we're just given the information via an .xls file...which we import to a SQLServer table.

I have some data that is given to me that has two columns (below is for an example):
Column A is an identifiying number, i.e. for a project
Column B is a comma separated list of account strings for the project

A sample layout of what we get via the .xls file might look like the following (Column A is to the left of the dashes, and Column B is to the right of the dashes):

AA.ProjectBuildTower ----- 2222, 3333, 4444, 5555
BB.ProjectBuildFence ----- X900, 6789, 9000, 9876

What I need to do is now haveprojects listed out in Column A with each of it's account strings in Column B like so:
AA.ProjectBuildTower ----- 2222
AA.ProjectBuildTower ----- 3333
AA.ProjectBuildTower ----- 4444
AA.ProjectBuildTower ----- 5555

BB.ProjectBuildFence ----- X900
BB.ProjectBuildFence ----- 6789
BB.ProjectBuildFence ----- 9000
BB.ProjectBuildFence ----- 9876

Any suggestions would greatly help!

Thanks!

 

[bborissov]

Check

http://tek-tips.com/viewthread.cfm?qid=1451986

Especially gmastros function [dbo].[Split] posted there.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLSister]

This FAQ should give you a starting place

http://www.tek-tips.com/faqs.cfm?fid=5207

There are a couple of others on this topic as well.

"NOTHING is more important in a database than integrity." ESquared

 

[TheInsider]

We don't own the database where this is housed...we're just given the information via an .xls file...which we import to a SQLServer table.

You can't modify the source, but what's stopping you from creating some code to process the data from the .xls file? Using a simple VBA routine, you could read each row's CSV column and use the Split() method to return an array of all the different account strings. Then, loop through the array and do an INSERT into the SQL Server database for each value.
i.e.
for each row / project id
a = Split()
for i = 0 to ubound(a)
insert into table project id, a(i)

I think that's the simplest way of doing it.

 

[gmmastros]

Unfortunately, the simple way is not usually the fastest way, either.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheInsider]

Who cares about speed? Obviously, if they're importing data from an Excel spreadsheet, efficiency isn't of utmost importance.

 

[gmmastros]

>> Who cares about speed?

I do.

You do make a good point though. If this is a 'once and done' situation, then it really doesn't matter. However, this file could be large, and if it's not coded properly, it could take a very long time to import it all. Add to that.... this file could be imported every day (or more often than that), and you could find yourself in a situation where performance becomes important.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheInsider]

Agreed. However, programmers far too often fall into the trap of seeking out the most elegant, ultra-efficient code when it's completely unnecessary to get the job done.

If you were hiring the original poster to write a routine to import this data, what would you rather have them produce? A) a routine that takes 35 seconds to run, each day, and took five minutes to write, or B) a routine that takes three seconds to run, each day, and five hours to perfect? Now, if we're talking about real-time requirements, then I can see the justification for the latter option. Otherwise, it just becomes a matter of ego.

It reminds me of how difficult it was for Jeff Bezos to get his programmers to produce the One-Click ordering system at Amazon. All he wanted was a single-click order, and the programmers kept coding it with "Are you sure?" type confirmation messages.

 

[gmmastros]

I think we're both saying the same thing.

My motto has always been. "Make it work, then make it fast". If it's already fast, then there's no need to make it faster.

If the difference was 3 seconds vs. 35 seconds, and this was run once a day, then 35 seconds is probably good enough. In my opinion, it's the "run once a day" that makes it good enough. I've been known to spend hours working on a query because it takes longer than 100 milliseconds (my personal goal). And of course, the more often a query is run, the more time I will spend optimizing it.

Of course, I'm not normal, either.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

I prefer a (B) routine. Call it ego but if that routine should run every day and it CAN be coded to run for 5 secs instead of 35 secs, I'll hire the man for 5 hours to write it. And believe me every cent will worth it.
You have customers out there and the will compare your speed with this of your competitors (if all other if almost equal). Nobody like to click a button and wait a minute (or two or more) to get the result.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

I agree, code that I write runs so fast that SQL server 2008 had to introduce nanoseconds precision because all my code runs always in 0 milliseconds :-0

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TheInsider]

Yeah, I do that too. ;-) I'll write something and then keep messing around with it in my spare time until I've optimized it as much as I can. It can be a good learning experience.

Guess we've now hijacked this thread. I wonder if the original poster ever solved their problem.

 

[codecomm]

I'm trying to use this example:

--*****************
--*****************

USE [ReportingDB]
GO
/****** Object: UserDefinedFunction [dbo].[fSplit] Script Date: 05/13/2008 12:03:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fSplit]
(
@List VARCHAR(6000),
@SplitOn VARCHAR(5)
)
RETURNS @RtnValue TABLE
(

ID INT identity(1,1),
Value VARCHAR(100)
)
AS
BEGIN
WHILE (Charindex(@SplitOn,@List)>0)
BEGIN
INSERT INTO
@RtnValue (value)
SELECT
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
END

INSERT INTO
@RtnValue (Value)
SELECT
Value = ltrim(rtrim(@List))

RETURN
END

--***************
--***************

I've added it to my UDFs under that database under table valued Functions, but when I run the following SQL:
SELECT colA, dbo.fSplit([Program Code], ',') AS Expr1
FROM mainTable

...I get:
Can't find column dbo, or user defined funciton or aggregate fSplit, or the name is ambiguous.

I'm sure it's something goofy I'm doing!

 

[TheInsider]

Isn't your function returning a table... so shouldn't you treat it as such in your SELECT statement?

 

[gmmastros]

codecomm,

In the code I show below, I am creating a table variable to represent your original data. I hardcoded your sample data in to this table so you can see how it works. You can copy/paste this code to a query window to see how it works.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](ColumnA [COLOR=blue]VarChar[/color](40), ColumnB [COLOR=blue]VarChar[/color](400))

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'AA.ProjectBuildTower'[/color],[COLOR=red]'2222, 3333, 4444, 5555'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'BB.ProjectBuildFence'[/color],[COLOR=red]'X900, 6789, 9000, 9876'[/color])

[COLOR=blue]Declare[/color] @Output [COLOR=blue]Table[/color](Header [COLOR=blue]VarChar[/color](40), Data [COLOR=blue]VarChar[/color](20))

[COLOR=blue]While[/color] Exists([COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Temp [COLOR=blue]Where[/color] [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]','[/color], ColumnB) > 0)
	[COLOR=blue]Begin[/color]
		[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @output(Header, Data)
		[COLOR=blue]Select[/color] ColumnA, [COLOR=#FF00FF]Left[/color](ColumnB, [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]','[/color], ColumnB)-1)
		[COLOR=blue]From[/color]   @Temp
		[COLOR=blue]Where[/color]  [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]','[/color], ColumnB) > 0

		[COLOR=blue]Update[/color] @Temp
		[COLOR=blue]Set[/color]    ColumnB = [COLOR=#FF00FF]LTrim[/color]([COLOR=#FF00FF]Right[/color](ColumnB, Len(ColumnB)-[COLOR=#FF00FF]CharIndex[/color]([COLOR=red]','[/color], ColumnB)))
	[COLOR=blue]End[/color]

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Output(Header, Data) 
[COLOR=blue]Select[/color] ColumnA, ColumnB
[COLOR=blue]From[/color]   @Temp

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Output [COLOR=blue]Order[/color] [COLOR=blue]By[/color] Header, Data

This code should work on SQL 2000 and SQL 2005.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

Of course, I'm not normal, either.

oh yes you are



you're at least 3NF

;-)

r937.com | rudy.ca

 

[AlexCuse]

a wise man once told me

the only thing normal about database people is their tables

(altered for the SQLSista's in the audience)

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[SQLSister]

Alex being normal is overrated (except in the database sense)

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

normalize until it hurts, denormalize until it works

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[codecomm]

gmmastros,

Thanks a ton..that did the trick!