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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply