comma separated cell data into rows

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!

Use the String breaker function...check the following...
CREATE FUNCTION dbo.udf_StringBreaker (@InputString Varchar(8000), @Delimiter Char(1) = ',' )
RETURNS @ResultTable TABLE(String1 Varchar(255) )

AS
BEGIN
WHILE CHARINDEX(@Delimiter, @InputString) <> 0
BEGIN
INSERT INTO @ResultTable
SELECT SUBSTRING(@InputString, 1, CHARINDEX(@Delimiter, @InputString) - 1)
SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(@Delimiter, @InputString) + 1, 8000)
END /* WHILE ... */
INSERT INTO @ResultTable
SELECT @InputString
RETURN
END

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!

Your function is a Table-function. So it has to be in the From clause of the Select statement:
SELECT Value FROM dbo.fSplit(ColAInputstring, ',')
Your problem is that you want to fill two columns in your table. Maybe you can use a Join of two of your table valued functions):
SELECT a.Value, b.Value
FROM dbo.fSplit(ColAInputstring, ',') a
JOIN dbo.fSplit(ColBInputstring, ',') b ON b.ID =a.ID
Alternatively you could build the logic for separating the values in the two commaseparated strings in one function.
(In your function in the statement
INSERT INTO ........ SELECT Value = ltrim(rtrim....
you can skip 'Value ='. It makes only a columnname in the SELECT, but looks like you are assigning ltrim(rtrim.... to a variable.)
Success, Cor Westra

All, this did the trick ...and thanks for you help!
Declare @Temp Table(ColumnA VarChar(40), ColumnB VarChar(400))
insert into @Temp Values('AA.ProjectBuildTower','2222, 3333, 4444, 5555')
insert into @Temp Values('BB.ProjectBuildFence','X900, 6789, 9000, 9876')
Declare @Output Table(Header VarChar(40), Data VarChar(20))
While Exists(Select * From @Temp Where CharIndex(',', ColumnB) > 0)
Begin
insert into @output(Header, Data)
Select ColumnA, Left(ColumnB, CharIndex(',', ColumnB)-1)
From @Temp
Where CharIndex(',', ColumnB) > 0
Update @Temp
Set ColumnB = LTrim(Right(ColumnB, Len(ColumnB)-CharIndex(',', ColumnB)))
End
Insert Into @Output(Header, Data)
Select ColumnA, ColumnB
From @Temp
Select * From @Output Order By Header, Data