table structure create table aa ( a varchar(25) , b int) insert into aa values('ass', 1) insert into aa values('ass1', 2) insert into aa values('ass2', 3) insert into aa values('ass3', 4) insert into aa values('ass4', 5) if the value in the column b is 3 the output should be inthe forma ----ass2 ass2 ass2
Welcome to the forum! That's a presentation issue, and nothing you should even try and think about in SQL.
in sql,declare @aa table ( a varchar(25) , b int) insert into @aa values('ass', 1)insert into @aa values('ass1', 2)insert into @aa values('ass2', 3)insert into @aa values('ass3', 4)insert into @aa values('ass4', 5)select a from @aa cross join master..spt_values where type='p' and number<b
Hm, sorry. I may have misunderstood your question. Can you explain how the expected result should be constructed from your sample data?
[quote user="FrankKalis"] Hm, sorry. I may have misunderstood your question. Can you explain how the expected result should be constructed from your sample data? [/quote] IIUC, The value of a should be as many times as that of value of b
hi thank u for ur reply this can be rewitten like thisdeclare @aa table ( a varchar(25) , b int) insert into @aa values('ass', 1)insert into @aa values('ass1', 2)insert into @aa values('ass2', 3)insert into @aa values('ass3', 4)insert into @aa values('ass4', 5) selecta .* from @aa a cross join @aa b where a.b>=b.band a.b= 3order by a.a
Okay, what about: SELECT a FROM @aa CROSS JOIN master..spt_values WHERE type='p' AND number <= b AND number > 0
