SQL Server Performance

select the recored in specific format

Discussion in 'Getting Started' started by prasannab, Sep 2, 2009.

  1. prasannab New Member

    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
  2. FrankKalis Moderator

    Welcome to the forum!
    That's a presentation issue, and nothing you should even try and think about in SQL.
  3. Madhivanan Moderator

    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
  4. FrankKalis Moderator

    Hm, sorry. I may have misunderstood your question. Can you explain how the expected result should be constructed from your sample data?
  5. Madhivanan Moderator

    [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
  6. prasannab New Member

    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
  7. FrankKalis Moderator

    Okay, what about:
    SELECT
    a
    FROM
    @aa
    CROSS JOIN
    master..spt_values
    WHERE
    type='p' AND
    number <= b AND
    number > 0

  8. FrankKalis Moderator

    ...or
    SELECT
    a
    FROM
    @aa
    CROSS JOIN
    master..spt_values
    WHERE
    type='p' AND
    number BETWEEN 1 AND b

Share This Page