SQL Server Performance

help plz

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

  1. erajendar New Member

    Dear All,


    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??



  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

  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

    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
    group by
  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
    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

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

  7. SanetteWessels New Member


    Is COALESCE undocumented? I found it in BOL.


    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


Share This Page