Split a field into rows and columns - SQL 2005

Question

Hi,

I have looked at all the forum posts on splitting a field in a SQL 2005 table into rows and columns and must say I am rather overwhelmed and confused with all the expert advice and I dont know which one to use.

Hopefully I will be forgiven for asking help in this area again.

My client has users that should be allowed to see values (members) in a dimension table "DEPARTMENT" based on a 'dimension values filter' populated on the user's card (record).

The user's card shows allowed filters in the following way:

DP1100|DP1113|DP1200..DP1250|DP2300|DP2360..DP2366|DP65400|DP777777

These are all values of one dimension, for instance departments and the individual values are not all of the same character length. (There are therefore two 'parameters' here: in and between)

The string above then means: access to DP1100,DP1113, between DP1200 and DP1250, DP2300, between DP2360 and DP2366, DP65400, DP777777

The aim is select values from table DEPARTMENT where the values are in a list of values from the filter

So ideally that filter should then translate into a temp table (Stored procedure? Function?) which looks like this:

**Value 1   Value 2**                    
DP1100                    
DP1113                     
DP1200      DP1250                     
DP2300                       
DP2360      DP2366                    
DP65400                     
DP777777

I would need a function that can be applied to all the filter fields in the user record, every time the select script checks for valid, allowed values for the user.

Help would be much appreciated!

Answer 1

declare @dept table            
(            
    dept	varchar(10)            
)            
            
insert into @dept (dept)            
select  'DP1100'	union all            
select  'DP1112'	union all            
select  'DP1113'	union all            
select  'DP1200'	union all            
select  'DP1210'	union all            
select  'DP1220'	union all            
select  'DP1230'	union all            
select  'DP2300'            
            
declare @usercard   varchar(100)            
            
select  @usercard	= 'DP1100|DP1113|DP1200..DP1250|DP2300|DP2360..DP2366|DP65400|DP777777'            
            
select  d.*            
from    @dept d            
    inner join            
    (            
    	select	start_value	= case 	when charindex('..', Data) <> 0            
    					then left(Data, charindex('..', Data) - 1)            
    					else Data            
    					end,            
    		end_value	= case 	when charindex('..', Data) <> 0            
    					then right(Data, len(Data) - charindex('..', Data) - 1)            
    					else Data            
    					end            
    	from	dbo.fnParseList('|', @usercard)            
    ) f	on	d.dept	>= start_value            
    	and	d.dept	<= end_value            
            
/*            
dept                   
----------             
DP1100            
DP1113            
DP1200            
DP1210            
DP1220            
DP1230            
DP2300            
            
(7 row(s) affected)            
*/

get fnParseList here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033&#315323

Split a field into rows and columns - SQL 2005

Follow this question

Related Questions