Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: sql statement for records summary

    hi
    suppose the recordset like this, that shows the cell phone models of nokia, siemens,...

    Code:
    IDModel Mark        ModelCaption
    1          nokia       6100
    2          nokia       6220
    3          nokia       6600
    4          siemens   mc65
    5          siemens   sx1
    now, i want to organize these records to somethin like this:
    Code:
    Mark        Models
    nokia       6100,6220,6600
    siemens    mc65,sx1

    how can i do it?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create function MarkModels(@Mark integer)
    Returns varchar(500) as
    begin

    Declare @ModelString varchar(500)

    set @ModelString = ''

    Select @ModelString = @ModelString + ModelCaption + ', ' from YourTable where Mark = @Mark

    return left(@ModelString, len(@ModelString) - 1)

    end

    Sorry if the syntax is off, but hopefully you'll see the method being used. You will have to include this function in the result set of your query, and the process may be slow if your table is large.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Posts
    144
    i posted this thread years ago, anyone can write a view for this?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This technique will not work as a view. Only as a function.
    Now, you could write a view that calls the function...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2006
    Posts
    2

    Please find the query and the function

    The below code is the function

    CREATE FUNCTION [dbo].[fn_getmodelcaptions] (@mark varchar(100))
    RETURNS varchar(500) AS
    BEGIN
    declare @modelcaptions varchar(500)

    select @modelcaptions = coalesce(modelcaption+','+@modelcaptions,modelcapt ion) from yourtable where mark = @mark

    return (@modelcaptions)
    END

    please find the query to get the desired result

    select mark,dbo.fn_getmodelcaptions(mark) from yourtable group by mark

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Did you have a specific question?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Dec 2006
    Posts
    2
    No I am replying to bono's question

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Kinda late, since that thread had been dormant for nearly two months...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •