none
STUFF Function

    Question

  • Greetings, I am using SQL Server 2008 R@ and i am stumped on trying to put all values from a field in my talble into one ROW.  example.  I have a Table called Items.  It only has ONE field called "SKU" (Char(8))  There are 5 records in this table.

    SKU

    1) = 0271234

    2) = 02745685

    3) = 02743518

    4) = 02734875

    5) = 02788742

    I would like to utilize the "STUFF" Function to have it return one row of data to include a quote and comma.  Please see desired result.

    Desired Result = '0271234','02745685','02743518','02734875','02788742'

    Is this possible?  Any assistance is greatly appreciated


    Paul

    Thursday, February 16, 2017 1:09 PM

Answers

  • Try this query:

    Declare @Items As Table (Sku Char(8))
    
    Insert Into @Items
    Values 
    	('0271234')
    	,('02745685')
    	,('02743518')
    	,('02734875')
    	,('02788742')
    
    Declare @Result AS VarChar(999)
    
    Select	@Result = IsNull(@Result  + ',', '') + '''' + Sku + ''''
    From		@Items
    
    Select @Result

    • Proposed as answer by Hilary Cotter Thursday, February 16, 2017 1:49 PM
    • Marked as answer by Paul 2 U Thursday, February 16, 2017 2:07 PM
    Thursday, February 16, 2017 1:45 PM

All replies

  • Try this query:

    Declare @Items As Table (Sku Char(8))
    
    Insert Into @Items
    Values 
    	('0271234')
    	,('02745685')
    	,('02743518')
    	,('02734875')
    	,('02788742')
    
    Declare @Result AS VarChar(999)
    
    Select	@Result = IsNull(@Result  + ',', '') + '''' + Sku + ''''
    From		@Items
    
    Select @Result

    • Proposed as answer by Hilary Cotter Thursday, February 16, 2017 1:49 PM
    • Marked as answer by Paul 2 U Thursday, February 16, 2017 2:07 PM
    Thursday, February 16, 2017 1:45 PM
  • Thank you so much for your quick response Malison.  This worked out great.

    Paul

    Thursday, February 16, 2017 3:42 PM