Group by? - SQL Server Forums

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

New to SQL Server Programming

Group by?

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-07-23 : 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:

[code]
CFSID AbuseTypeIDs
989891 1929
989891 1312
989891 8789
787871 1929
787871 1987
787871 2322
643244 1879

[code]

so how do i accomplish this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 11:56:29

see scenario 4

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

parsevalues function can be found below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-07-23 : 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

52326 Posts

Posted - 2012-07-23 : 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

651 Posts

Posted - 2012-07-23 : 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

52326 Posts

Posted - 2012-07-23 : 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

651 Posts

Posted - 2012-07-24 : 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

52326 Posts

Posted - 2012-07-24 : 09:32:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources