Extract delimited string into a View (?)

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

Extract delimited string into a View (?)

Author

Topic

danj
Starting Member

2 Posts

Posted - 2012-08-22 : 12:25:08

Hi all

I hope you can help a non-SQL person with something slightly too difficult for me! I have a table in a SQL2005 DB which contains records entered by users of an ASP.Net website, to register their applications with my Active Directory team. Here is a simplified version:


AppID    Attributes
123-1    badPwdCount;sAMAccountName;displayName
524-5    givenName;displayName;mail                etc

There will be some 4000 lines once complete. I want to take the ';' delimited string in the 'Attributes' field and populate a table or view or something to serve as a tally of attributes:


Attribute      Count
badPwdCount    1
givenName      1
displayName    2             etc

for every attribute identified in the string, it must add the row if it is not there (setting count to 1) and increment the count if it is
already present. It must also be a live view of the actual data in the main table (in case a user updates their choices on the website, I don't want to have to run a job to update the attributes table), which is why I was thinking about views.

How do I go about this? I wrote a table valued function to split the string on the ';' delimiter, so now I have a table of attributes for each record in the main table, and some confusion on what to do next

In C# or whatever I would use some sort of foreach loop but not sure how this works in T-SQL. Can anyone give me any pointers?

thanks

Dan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 12:33:07

if you've table valued function then you can do like this


SELECT f.Field,COUNT(*) AS Cnt
FROM YourTable t
CROSS APPLY dbo.YourTableValuedFunction(t.Attributes,yourdelimiter)f
GROUP BY f.Field

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

danj
Starting Member

2 Posts

Posted - 2012-08-23 : 05:07:17

Visakh it seems that CROSS APPLY is EXACTLY what I wanted, many thanks. I will create a view using this query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 10:12:54

quote:
Originally posted by danj

Visakh it seems that CROSS APPLY is EXACTLY what I wanted, many thanks. I will create a view using this query.

welcome

see what all you can do with it

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources