Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Posts
    14

    Unanswered: column data in the single row

    Hi,
    I have a table such as

    ID Name OS
    ----------------------------------
    10 Paul AIX
    10 Paul SOLARIS
    10 Paul NT
    20 Jack NT
    20 Jack SOLARIS

    and I have asked to create an output as

    ID NAME OS
    -----------------------------------
    10 Paul AIX,SOLARIS,NT
    20 JAck NT,SOLARIS

    How can I get this output via sql.
    Also a good source for such tricky SQLs would be very fruity.

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    Hi Faar,

    If this is a one-time deal for a report, then I would go ahead and plug the dreaded cursor within a cursor. If your example table is named testing and is defined as such:

    create table testing
    (
    ID int,
    Name varchar(30),
    OS varchar(100)
    )

    -and your values are as you provided. Then the code below should work:

    declare @id int,
    @name varchar(30),
    @OS varchar(100),
    @CurrentOS varchar(100)

    create table #formatted
    (
    ID int,
    Name varchar(30),
    OS varchar(100)
    )

    declare person cursor for
    select id, name from testing
    group by id, name

    open person

    fetch person into @id, @name
    while @@fetch_status = 0
    begin
    set @OS = ''
    declare OS cursor for
    Select OS from testing
    where ID = @ID
    group by OS

    open OS
    fetch OS into @CurrentOS

    while @@fetch_status = 0
    begin
    set @OS = @OS + @CurrentOS + ', '
    fetch OS into @CurrentOS
    end
    Set @OS = Left(@OS,LEN(@OS)-1)
    close OS
    deallocate OS

    insert #formatted (id, name, os)
    values (@id, @name, @OS)

    fetch person into @id, @name
    end
    close person
    deallocate person

    select * from #formatted

    drop table #formatted

    --This is pretty much textbook for bad sql - but if you only need to to this once I wouldn't worry about it. If you need to do this regularly, there are better performing methods than the cursors such as cycling through a table variable.

    good luck.
    Kit Lemmonds

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Warning! Untested code. May have syntax errors...
    Code:
    create function OSList(@ID integer)
    returns varchar(500)
    as
    begin
    declare	@ReturnValue varchar(500)
    select	@ReturnValue = isnull(@ReturnValue + ', ', '') + OS
    from	[YourTable]
    where	ID = @ID
    order by OS
    Return @ReturnValue
    end
    To execute:
    Code:
    select	distinct
    	ID,
    	Name,
    	dbo.OSList(ID)
    from	[YourTable]
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Apr 2005
    Posts
    14
    hi,
    "create function " suggestion works very well.
    thanks everybody.


    Do you know a good source for such tricky SQLs?

  5. #5
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Just search any of the SQL Server forum like :
    SQLTeam.com
    SQLServerCentral.com
    SQL-Server-Performance.com
    forums.microsoft.com
    -----------------
    KH


  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Celko has written good books on SQL.
    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
  •