February 8, 2009 at 2:14 pm
hi folks,
here is a small tricky query to develop ..the requirement is
i have a table named dbo.alpha
it has a single column named 'letters' and it had 4 values(rows) in it, named as 'T','E','S','T'
so it looks like this..
DBO.ALPHA
Letters
T
E
S
T
Now i want to develop a query which shows my out put result as a string 'TEST'
and dont want to alter my table or its contents..i just want to display in the format as consecutive letters 'TEST' in the output.
thanking you..
February 8, 2009 at 7:29 pm
What have you tried so far? what is the real, full CREATE statement for dbo.Alpha? is there a primary key?
how do you know what row either "T" is ? there's no id1 or anything. are you familiar with rownumber() if there's no PK? can this come from a CTE? what limitations did the professor put on this assignment?
Lowell
February 8, 2009 at 10:01 pm
dude,
if u really want the reply then provide us the complete tabel structure and some more sample data then only we can help u out ...
Mithun
February 8, 2009 at 10:19 pm
I believe this might help..
DECLARE @tbTemp TABLE(VCH VARCHAR(50))
DECLARE @vchOutput VARCHAR(MAX)
SET @vchOutput= ''
INSERT INTO @tbTemp SELECT 'T'
INSERT INTO @tbTemp SELECT 'E'
INSERT INTO @tbTemp SELECT 'S'
INSERT INTO @tbTemp SELECT 'T'
UPDATE
@tbTemp
SET
@vchOutput= @vchOutput+ VCH
--------------------------
SELECT @vchOutput Result
--------------------------
This is your output.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
February 8, 2009 at 10:39 pm
HEY DUDE,,'
TRY THIS
DECLARE @MITHUN varCHAR (40)
SELECT @MITHUN = ''
SELECT @MITHUN = @MITHUN + name FROM Table_1
SELECT top 1 replace (@MITHUN,' ',',') FROM Table_1
ITS SURLY GONNA WORK .......'
mITHUN
February 8, 2009 at 11:03 pm
Hi,
Please use the below scripts to reach your goal. hope, it may help you.
declare @finalvar varchar(100)
declare @initVar int,@increVar int
set @finalvar=''
set @initVar=0
set @increVar=1
create table #tmp_letters(id int identity(1,1),letters char(1))
insert into #tmp_letters
select * from #sampletest
select @initVar=count(letters) from #tmp_letters
while(@increVar<=@initVar)
begin
select @finalvar=@finalvar+letters from #tmp_letters where id=@increVar
set @increVar=@increVar+1
end
select @finalvar
February 9, 2009 at 12:26 am
Try this....
declare @wordstr varchar(500)
set @wordstr = ' '
select @wordstr = @wordstr +letters from dbo.alpha
select ltrim(rtrim(@wordstr))
February 9, 2009 at 2:06 am
Hi nagesh,
The solution you have specified wont work as it gets only the last row data and appends to your local variable.
That is for the example provided by ashy,
declare @wordstr varchar(500)
set @wordstr = ' '
select @wordstr = @wordstr +letters from dbo.alpha
select ltrim(rtrim(@wordstr))
Gives, @wordstr As 'T'
Please try out.
Thanks.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
February 9, 2009 at 3:02 am
Hi,
try the following simple script:
-------------------------------------------------------------------------------
declare @count int;
declare @counter int;
declare @STR varchar(20);
declare @temp varchar(1)
declare @tbl table
(
id smallint identity,
alpha varchar(20)
)
insert into @tbl select a from alpha;
select @count=count(1) from @tbl;
set @STR='';
set @temp='';
set @counter=1;
while @counter <= @count
begin
set @temp=(select alpha from @tbl where id=@counter);
set @STR=@str+@temp;
set @counter=@counter+1
end
print @STR;
---------------------------------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply