| Author |
 Topic  |
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
 Posted - 07/23/2012 : 11:44:58
|
So i have a table with these values:
CFSID AbuseTypeIDs
989891 1929,1312,8789
787871 1929, 1987, 2322
643244 1879
What i need to do is break out those AbuseTypeIDs where that column has ONE value only, no commas, still associated with the same CFSID value. So it should look like this:
CFSID AbuseTypeIDs
989891 1929
989891 1312
989891 8789
787871 1929
787871 1987
787871 2322
643244 1879
so how do i accomplish this? |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 07/23/2012 : 12:02:47
|
| The parsing you're suggesting doesn't really apply because it uses a different ID for each row. I need to rows to duplicate the CFSID value to match with each AbuseTypeIDs value in that column. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 07/23/2012 : 12:09:12
|
quote:
Originally posted by WJHamel
The parsing you're suggesting doesn't really apply because it uses a different ID for each row. I need to rows to duplicate the CFSID value to match with each AbuseTypeIDs value in that column.
thats not an issue. you just need to select ID from you main table not from function
you just need to create function and use it like below
SELECT t.CFSID,f.Val
FROM yourtable t
CROSS APPLY dbo.ParseValues(t.AbuseTypeIDs) f
try it and you'll see what i mean
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 07/23/2012 : 21:54:29
|
For some reason, i'm getting the following error when trying to apply the function against that table:
Msg 313, Level 16, State 3, Line 3
An insufficient number of arguments were supplied for the procedure or function dbo.ParseValues.
Using my table name in the query looks like this:
SELECT t.CFSID,f.Val
FROM douglasconversion.dbo.victimdescriptors t
CROSS APPLY dbo.ParseValues(t.AbuseTypeIDs) f |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 07/23/2012 : 22:18:55
|
quote:
Originally posted by WJHamel
For some reason, i'm getting the following error when trying to apply the function against that table:
Msg 313, Level 16, State 3, Line 3
An insufficient number of arguments were supplied for the procedure or function dbo.ParseValues.
Using my table name in the query looks like this:
SELECT t.CFSID,f.Val
FROM douglasconversion.dbo.victimdescriptors t
CROSS APPLY dbo.ParseValues(t.AbuseTypeIDs,',') f
oops sorry there was a typo.
use modified suggestion as above
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 07/24/2012 : 06:05:04
|
| I know it's going to be a GREAT day when the error was not mine, but Visakhs! ;-) Thanks as usual. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 07/24/2012 : 09:32:14
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Group by?
