SQL Query Help..... - 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

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL Query Help.....

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-12-06 : 10:48:58

Folks:

I need help with a SQL Query/SP. I have table which stores Directory Names and it's permissions in a table in this format.

DirNames					READACCESS			WRITEACCESS			FULLACCESS
BACKUPS, RESTORES, DISASTER, EBOOKS, OFFICE,	true false false true true	false true true false false	false false true true false

I would like the output to be in this format:

DirNames	READACCESS	WRITEACCESS	FULLACCESS
BACKUPS		TRUE		FALSE		FALSE
RESTORES	FALSE		TRUE		FALSE
DISASTER	FALSE		TRUE		TRUE
EBOOKS		TRUE		FALSE		TRUE
OFFICE		TRUE		FALSE		FALSE

Thanks !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 12:08:22

this is not a good way of storing values in tables. Have you heard about normalisation and first normal form? By storing like you're really making manipulations difficult and costly

Anyways, if you want to live with this you can use a logic like below and its rather dirty!


SELECT p.Val AS DirNames,
q.Val AS READACCESS,
r.Val AS WRITEACCESS,
s.Val AS FULLACCESS
FROM
(
SELECT f.ID,f.Val
FROM table t
CROSS APPLY dbo.ParseValues(t.DirNames,',')f
)p
INNER JOIN
(
SELECT f.ID,f.Val
FROM table t
CROSS APPLY dbo.ParseValues(t.READACCESS,' ')f
)q
ON q.ID = p.ID
INNERJOIN
(
SELECT f.ID,f.Val
FROM table t
CROSS APPLY dbo.ParseValues(t.WRITEACCESS,' ')f
)r
ON r.ID = q.ID
INNER JOIN
(
SELECT f.ID,f.Val
FROM table t
CROSS APPLY dbo.ParseValues(t.FULLACCESS,' ')f
)s
ON s.ID = r.ID

ParseValues can be found in below link
http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources