May 16, 2011 at 12:05 pm
I need to build a string dynamically, which has values of all fields from the table-one. While building string, i need place value in specific order (order and position of field is defined in table-two below). In order to place value in specifc order, I should be reading table-two dynamically for the field for its value length, startAt. If length of value is less that defined length, then I need to put white-spaces after the value. for example Fname should be 10 characters long, and FirstName is Joe then string should have "Joe......."
Table-One: It has hundreds of fields with data.
For example:
Fname | Lname | Age | Sex | … more columns etc
Steve | Martin | 32 | M | ….
Table-Two: It has all fields name from table name as data with some other details.
coumn_Name | Length | startAt | EndAt
Fname | 10 | 15 | 24
Lname | 10 | 25 | 34
Age | 2 | 5 | 7
Sex | 1 | 35 | 35
So based on above example I need to build a string where each value for each field need to placed in specific orther defined in table-two by using value position: startAt, and endAt. So the output string should looks like below. I have indicated space with dots (…) here.
"….32……..Steve…..Martin….M....."
Please refer to table to understand value position
On above stirng, since age starts at 5th position, I have four white-space (indicated by dots) before 32, and then value 32. In the same way since FirstName starts at 15, I have additional white-spaces to make 15th place where you see name "Steve"..... In the same way lastname, and sex is placed.
After I finish building above string, I will be feeding that string to DLL assembly to do some specific job.
I hope it makes sense.
Thank you
May 16, 2011 at 12:39 pm
That is totally unclear to me what you are trying to do. Please see the link in my signature about how to post your question so you have the best at getting help. Without some ddl, sample data, desired output and a clear explanation of the problem there is no way anybody can help you.
_______________________________________________________________
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/
May 18, 2011 at 10:04 am
i have updated my question. I hope it is clear enough for you to understand
May 18, 2011 at 1:02 pm
SMB2010 (5/18/2011)
i have updated my question. I hope it is clear enough for you to understand
You kind of missed the whole point about posting ddl and sample data that is readily consumable. In other words you need to some of the leg work so that people volunteering to help you (like myself) don't have to create it for you.
Something like this.
create table #MyTable
(
Fname varchar(15),
LName varchar(25),
Age tinyint,
Sex varchar(1)
)
insert #MyTable (FName, LName, Age, Sex) values('Steve', 'Martin', 32, 'M')
create table #MyControl
(
ColumnName varchar(25),
FieldLength int,
StartAtPos int,
EndAtPos int
)
insert #MyControl
Values
('FName', 10, 15, 24), ('LName', 10, 25, 34), ('Age', 2, 5, 7), ('Sex', 1, 35, 35)
OK, so with this as a starting point. I don't think you need the EndAtPos because the values you provided are not accurate and additionally you have the StartPos and Length so it is easy to calculate as needed.
Something like this should get you in the right direction. You should read and understand this code because it will be you who has to support this at 3am if it fails in production. :w00t:
declare @Length int --this will hold the total record length
declare @StartPos int --this is where the first value should appear since the sample did not start in the first column
select @Length = MAX(StartAtPos + FieldLength - 1), @StartPos = MIN(StartAtPos) from #MyControl
declare @Result varchar(max)
set @Result = REPLICATE(' ', @Length)
select top 1 @Result = STUFF((
select 'right(REPLICATE('' '', ' + cast(StartAtPos + FieldLength - 1 as varchar(25)) + ') + ' + ColumnName + ', ' + cast(StartAtPos + FieldLength - 1 as varchar(25)) + ') + '
from #MyControl
order by StartAtPos
for xml path('')), 1, 0, '')
from #MyControl
set @Result = 'select Replicate('' '', ' + cast(@StartPos as varchar(10)) + ') + ' + SUBSTRING(@Result, 0, DATALENGTH(@Result) - 2) + ' from #MyTable'
--select @Result
exec(@Result)
drop table #MyControl
drop table #MyTable
_______________________________________________________________
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply