Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Post Unanswered: Merge multiple values in field - MS SQL

    I need help writing a query. I have this information but I need to merge the data of multiple fields in one field.

    Raw Data:
    Code Size

    9060 S
    9060 M
    9060 L
    9060 XL

    How can I get this results,

    Code sizes
    9060 S, M,L,XL

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can do this in SQL but it is best achieved in the front end. This is a presentation issue, so write something in Access, Reporting Services, php (whatever you use to display the data) to do this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2007
    Posts
    11

    Thumbs down

    create table #tmp(Code int,[Size] varchar(10))

    insert #tmp Values (9060,'S')
    insert #tmp Values (9060, 'M')
    insert #tmp Values (9060, 'L')
    insert #tmp Values (9060, 'XL')
    insert #tmp Values (9061,'S')
    insert #tmp Values (9061, 'M')
    insert #tmp Values (9062, 'L')
    insert #tmp Values (9062, 'XL')



    Select * from #tmp

    SELECT
    Code,Left(Size,Len(Size)-Len(',')) AS Size
    From
    (SELECT distinct Code from #tmp ) T1
    CROSS APPLY
    (SELECT convert(varchar(1000),Size) + ',' from #tmp T2 where T1.Code=T2.Code FOR XML PATH('')) T (Size)

  4. #4
    Join Date
    Oct 2009
    Posts
    2
    I need a better solution, since this data comes from invoices and 1 invoice can have up to 3000 lines of data.

    Tables such as,

    Code
    Size

    Comes separately. I just need to merge "sizes" and group "code" to get only one line of data.

  5. #5
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetSizeString]') and xtype in (N'FN', N'IF', N'TF'))
    drop FUNCTION [dbo].[fn_GetSizeString]
    GO
    create function fn_GetSizeString(@Code int)
    returns nvarchar(4000)
    as
    begin
    	Declare @SizeString nvarchar(4000)
    	Set @SizeString = ''
    	Select	@SizeString = @SizeString + case when @SizeString = '' Then [Size] else ', '+ [Size] End
    	From	tmp
    	Where	Code = @Code
    
    	Return @SizeString
    End
    Go
    
    select code,dbo.fn_GetSizeString(code) from tmp group by code

Posting Permissions

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