May 13, 2008 at 11:19 am
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!
May 13, 2008 at 11:25 am
Search for SPLIT function on this site. You'll most likely need a variation of that.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 13, 2008 at 11:27 am
Ahh...will do!
Thanks for the heads up!
May 13, 2008 at 11:57 am
There was recently an article on this site about how to do this kind of thing very efficiently.
http://qa.sqlservercentral.com/articles/TSQL/62867/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 12:02 pm
Thanks for the replies!
I'm trying to use this example:
http://qa.sqlservercentral.com/scripts/T-SQL+Aids/31871/
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!
May 13, 2008 at 12:28 pm
Don't use that particular function. Used correctly, it will get the job done, but there are better ways to do this.
Here's a sample that works much better:
select top 100001 identity(int,0,1) as Number
into dbo.Numbers
from sys.all_objects s1
cross join sys.all_objects s2
go
create table #T (
ID int identity primary key,
Val1 varchar(100),
Val2 varchar(1000))
insert into #t (val1, val2)
select 'a', '1,2,3' union all
select 'b', '456,789,012'
select val1,
substring(val2+',', number,
charindex(',', val2+',', number) - number) as Parsed,
row_number() over (partition by val1 order by number) as Row
from #t
inner join dbo.numbers
on number <= len(val2)
and substring(',' + val2, number, 1) = ','
order by val1, number
You'll need to plug in your table and column names for it, of course. This will also create a Numbers table in your database. You'll want to use that again for other things, so keep that. If you really don't want it, make it a temp table for this. But read the article I linked earlier to see some of the uses of one before you make that decision.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 12:29 pm
P.S.: If you really want to use that function, you'll need to put it in your From clause, using CROSS APPLY, instead of in your Select clause. Inline functions go in the Select clause, table functions go in From (that's an over-simplification, but it points you in the right direction).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 14, 2008 at 10:07 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply