comma separated cell into rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

comma separated cell into rows

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2008-05-13 : 13:16:19

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!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 13:22:53

Here's one way: http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2008-05-13 : 14:12:05

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!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 14:13:41

fSplit is a table-valued function, yet you are calling it like a scalar-valued function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-13 : 14:29:01

SELECT mainTable.colA, split.Value
FROM mainTable
CROSS APPLY dbo.fSplit(mainTable.[Program Code], ',') split

- Jeff
http://weblogs.sqlteam.com/JeffS

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2008-05-14 : 12:08:21

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 12:49:47

Not sure why you'd want to do it that way, but okay.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-14 : 13:32:02

bubberz -- what was wrong with cross apply? Not complicated enough? Too efficient?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources