December 6, 2011 at 9:00 am
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. (Column Names: DirNames, READACCESS, WRITEACCESS, FULLACCESS)
DirNames: BACKUPS, RESTORES, DISASTER, EBOOKS, OFFICE,
READACCESS: true false false true true
WRITEACCESS: false true true false false
FULLACCESS: false false true true false
I would like the output to be in this format:
DirNames READACCESSWRITEACCESSFULLACCESS
BACKUPSTRUEFALSEFALSE
RESTORESFALSETRUEFALSE
DISASTERFALSETRUETRUE
EBOOKSTRUEFALSETRUE
OFFICETRUEFALSEFALSE
Thanks !
December 6, 2011 at 9:07 am
you say the table has four columns in the following order
DirName | ReadAccess | WriteAccess | FullAccess
And you want the table to be returned as
DirName | ReadAccess | WriteAccess | FullAccess
This would just be a simple select
If I have totally misunderstood, please provide create table statements, sample data insert statements, what you have tried so far and how you want the results to look with the above sample data so we can assit you better
December 6, 2011 at 9:10 am
Yes, the table has 4 columns but the data for 5 directores and it's permissions is stored in single record. I want the output to have 5 rows instead of 1.
DirNames|READACCESS|WRITEACCESS|FULLACCESS
The Attachment 'DataInTable.xls' is how the data is stored.
The Attachment 'ExpectedOutput.xls' is how I would like to see the output.
December 6, 2011 at 9:19 am
what you need is a string spliter then if they are all in one field but you want to break it into 5.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
the above should help, you could use , in the DirNames as the spliting point and the space in the rest
December 6, 2011 at 9:22 am
Then you should normalize this data and store it in the format as you have it displayed. A column should contain a single piece of information and not a whole set.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 6, 2011 at 10:06 am
Since you are new around here you probably have not yet had time to read best practices on posting questions. The idea is to make it easy for the unpaid volunteers to start work on your problem by providing as much information as possible. This typically means ddl (create table scripts), sample data (insert statements) and desired output based on the sample data. Take a look at the first link in my signature for ideas on how to post this type of stuff in the future. The benefit to you is that more people will be willing to help and you will get tested and fast code.
I went ahead and took the liberty of creating ddl and sample data for you. I have to say that you really need to consider keeping this data in the format presented as your desired output. Putting multiple rows of data in a single cell is nothing short of a total PITA. As you have discovered getting this stuff out in a usable format is really difficult.
Before I hand over a solution i suggest you read the article linked above, it can also be found in my signature about splitting strings. This is a great article but it is also highly technical. I employed the splitter function judiciously in my code. You need to read this and understand what it does and how it works BEFORE you put in production. Make sure you test this thoroughly. Keep in mind that at 3am when your code causes issues it will be you they call to fix it. One thing I did was add an identity column to your data so you have something to keep the order correct when parsing the additional columns.
create table #Combined
(
RowNum int identity, --need to add a identifier for each row
DirNames varchar(100),
ReadAccess varchar(100),
WriteAccess varchar(100),
FullAccess varchar(100)
)
insert #Combined
select 'BACKUPS, RESTORES, DISASTER, EBOOKS, OFFICE,', 'true false false true true', 'false true true false false', 'false false true true false'
create table #Parsed
(
RowNum int,
DirName varchar(100),
ReadAccess bit,
WriteAccess bit,
FullAccess bit
)
insert #Parsed (DirName, RowNum)
select names.Item as DirName, ItemNumber
from #Combined c
cross apply DelimitedSplit8K(replace(left(c.DirNames, datalength(c.DirNames) -1), ' ', ''), ',') names
update #Parsed set ReadAccess = case v.ReadAccess when 'True' then 1 else 0 end
from
(
select names.Item as ReadAccess, ItemNumber
from #Combined c
cross apply DelimitedSplit8K(ReadAccess, ' ') names
) v
join #Parsed p on p.RowNum = v.ItemNumber
update #Parsed set WriteAccess = case v.WriteAccess when 'True' then 1 else 0 end
from
(
select names.Item as WriteAccess, ItemNumber
from #Combined c
cross apply DelimitedSplit8K(WriteAccess, ' ') names
) v
join #Parsed p on p.RowNum = v.ItemNumber
update #Parsed set FullAccess = case v.FullAccess when 'True' then 1 else 0 end
from
(
select names.Item as FullAccess, ItemNumber
from #Combined c
cross apply DelimitedSplit8K(FullAccess, ' ') names
) v
join #Parsed p on p.RowNum = v.ItemNumber
select * from #Combined
select * from #Parsed
drop table #Combined
drop table #Parsed
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply