SQL Server Performance

help plz

Discussion in 'General Developer Questions' started by erajendar, Sep 17, 2003.

  1. erajendar New Member

    Dear All,

    Greetings!!

    I've a table like this:

    No Desc
    -- ----
    1 one1
    1 one2
    1 one3
    2 two1
    2 two2
    2 two3
    3 three1
    3 three2
    3 three3
    3 three4
    The no. repeats maximum 4 times(ie. 1 is there in above table 3 times and 3 is repeated 4 times and so on.)

    Now I want to display the above records like below using SQL query:

    No desc1 desc2 desc3 desc4
    ------------------------------
    1 one1 one2 one3
    2 two1 two2 two3
    3 three1 three2 threee3 three4

    Can anyone help me out please??

    regards,

    Rajendar


    ok
  2. Twan New Member

    Hi ya,

    This is really a client side operation...

    you may be able to do it using a function and a cursor within that function and then do

    select distinct no, fn_get_desc( no ) from table

    But if possible I'd do this on the client machine by ordering the result set and looping through it

    Cheers
    Twan
  3. FrankKalis Moderator

  4. Twan New Member

    It may be ok if you have a sequence number for each description as in:


    No Seq Desc
    -- --- ----
    1 1 one1
    1 2 one2
    1 3 one3
    2 1 two1
    2 2 two2
    2 3 two3
    3 1 three1
    3 2 three2
    3 3 three3
    3 4 three4


    select
    No,
    max( case Seq when 1 then Desc else NULL end ) as desc1,
    max( case Seq when 2 then Desc else NULL end ) as desc2,
    max( case Seq when 3 then Desc else NULL end ) as desc3,
    max( case Seq when 4 then Desc else NULL end ) as desc4
    from
    table
    group by
    No
  5. SanetteWessels New Member

    I generated this a while ago for someone else.......



    declare @tbl_Test TABLE (ID INT, Text VARCHAR(10))
    declare @tbl_New TABLE (ID INT, Text VARCHAR(1000))
    declare @cItemList varchar(8000)

    INSERT INTO @tbl_Test VALUES(1,'Text 1')
    INSERT INTO @tbl_Test VALUES(1,'Text 2')
    INSERT INTO @tbl_Test VALUES(1,'Text 3')
    INSERT INTO @tbl_Test VALUES(2,'Text 1')
    INSERT INTO @tbl_Test VALUES(2,'Text 2')
    INSERT INTO @tbl_Test VALUES(3,'Text 1')
    INSERT INTO @tbl_Test VALUES(3,'Text 2')
    INSERT INTO @tbl_Test VALUES(3,'Text 3')
    INSERT INTO @tbl_Test VALUES(3,'Text 4')
    INSERT INTO @tbl_Test VALUES(4,'Text 1')

    DECLARE @Min INT, @Max INT
    select @Min = min(ID) from @tbl_Test
    select @Max = max(ID) from @tbl_Test
    while @Min <= @Max
    begin
    SELECT @cItemList = COALESCE(@cItemList + ',' , '') + CAST(Text AS varchar(10))
    FROM @tbl_Test
    where ID = @Min

    INSERT INTO @tbl_New
    VALUES (@Min,@cItemList)
    SET @Min = @Min + 1
    SET @cItemList = NULL

    end
    SELECT * from @tbl_New

    SQL Developer
    JustEnough Software Corporation
    Walking on water and coding of a spec is easy, as long as both are frozen - A friend
  6. Twan New Member

    yep that would work too, although

    NOTE that that is an undocumented feature, and is not ANSI standard. It is likely to break in future releases of SQLServer...

    Cheers
    Twan
  7. SanetteWessels New Member

    Twan,

    Is COALESCE undocumented? I found it in BOL.

    Sanette

    SQL Developer
    JustEnough Software Corporation
    Walking on water and coding of a spec is easy, as long as both are frozen - A friend
  8. Twan New Member


    Hi Sanette,

    coalesce is documented, but the concatenation of column values into a variable as the statement traverses rows is.

    The normal behavious would be that the variable takes on the value of each row independently, so in your example it would result in being the last Text in your result set

    Cheers
    Twan

Share This Page