Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Unanswered: Combining multiple rows into a single row

    I've been working on this for a week but have yet to find a solution. I'm losing hair over this. Any & all help would be appreciated. Thanks a lot.

    1. Using SQL Server 2000.
    2. Need to combine multiple rows in query's result to 1 row and need to do this for more than 30 tables/fields. If possible, need to do this using function / stored procedure.
    3. I've written a function but there's an error and I'm can't find a workaround. The function is:
    -----------------------------------------
    create function [dbo].[MergeRows] (
    @query varchar(5000),
    @field varchar(50),
    @separator varchar(10)
    )
    returns varchar(4000) as
    begin

    declare @exec as nvarchar(4000)
    declare @retval as nvarchar(4000)
    declare @result as nvarchar(4000)

    set @exec = 'begin '
    set @exec = @exec + 'declare @return as varchar(4000) '
    set @exec = @exec + 'select @return = coalesce(@return + ''' + @separator + ''', '''') + cast(' + @field + ' as varchar(1000)) '
    set @exec = @exec + 'from (' + @query + ')q1 order by ' + @field + ' '
    set @exec = @exec + 'select @return end'

    exec sp_executesql @exec, N'@result nvarchar(4000) output', @result = @retval output

    return @retval

    end
    -----------------------------------------
    4. I used the following to test:
    select dbo.mergerows('select accountname from account', 'accountname', ';')
    and the error is:
    Only functions and extended stored procedures can be executed from within a function.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    the straightforward answer to this is that SQL (the language) is very poor at producing this sort of result set, all for good reason that is not worth expanding here.

    If this resultset is consumed by a reporting tool of some sort (reporting services, Access, Excel, ASP.NET etc.) then it will be far easier to return a table of data to that and do your row merging there. RDBMSs excel at storing and retrieving data. Reporting tools excel at manipulating and formatting datasets.

  3. #3
    Join Date
    Feb 2007
    Posts
    38
    Hi

    What you are looking for is crosstab report which converts rows to columns. MS SQL 2005 introduced PIVOT but in your case it may not work properly as you need to dispaly unknown values which comes from a 3rd table. For your convenience, this is URL that you can refer to Stored procedure: A simple way to perform crosstab operations

    Once you download the sp_CrossTab (at the bottom of the page), put it in the Master database. Then use the following script to test it.

    IF OBJECT_ID('tempdb..#account') IS NOT NULL
    drop table #account

    create table #account
    (
    id bigint identity(1,1),
    AccountName varchar(50),
    PurchaseDate datetime not null,
    PurchaseAmount float not null
    )

    insert #account Values('Bill Gates', '2009-01-11', 300)
    insert #account Values('David Jung', '2009-01-11', 100)
    insert #account Values('Steve Jobs', '2009-01-11', 200)
    insert #account Values('Tom Hanks', '2009-01-21', 300)
    insert #account Values('Julia Roberts', '2009-01-21', 100)
    insert #account Values('Mel Gibson', '2009-01-21', 200)

    EXECUTE sp_Crosstab
    'select AccountName,PurchaseAmount,datepart(year,PurchaseD ate) [PurcharYear] from #account'
    ,NULL
    ,NULL
    ,'AccountName'
    ,'PurchaseAmount'
    ,'sum'

    It may not directly give you the solution but still it may give you something to try, I hope.

  4. #4
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by pootle flump View Post
    the straightforward answer to this is that SQL (the language) is very poor at producing this sort of result set, all for good reason that is not worth expanding here.
    Tell me about it I learned the hard way when a varchar variable exceeded 512KB in a SELECT @var=@var+col FROM SomeTable which resulted in a massive attack on tempdb due to a threshold on variables. If you thought sort and hash spills to tempdb was bad, wait 'till you see what happens when a variable exceeds 512KB.

    Do what Pottle suggests and move your string manipulation to the presentation layer.

Posting Permissions

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