Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
resultset of a column displayed as line

已答复 resultset of a column displayed as line

  • Monday, January 28, 2013 12:49 PM
     
     
    Sign In to Vote
    0

    hey

    is there an easy way to get a resultset of a column into a line?

    like
    mytable
    field1 field 2
    abc  123
    def   345
    ghi 789

    select field1 from mytable;

    output should be in ONE line: abc def ghi

    thx
    greets

     

All Replies

  • Monday, January 28, 2013 12:53 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Try the below:

    DECLARE  @NamesTable TABLE (Id INT,
                            Name NVARCHAR(50))
INSERT INTO @NamesTable VALUES (1,'A'),
                               (2,'D'),
                               (2,'C'),
                               (3,'E'),
                               (3,'H'),
                               (3,'G')
--Option 01
DECLARE @listStr VARCHAR(MAX) --DO NOT initialize this one!
SELECT @listStr = COALESCE(@listStr + ',' ,'') + nt.Name
FROM @NamesTable nt
SELECT @listStr
--Option 02
; WITH CommaSeparatedXML (CommaSeparatedXML)
AS (SELECT CAST((SELECT (',' + nt.Name)
                 FROM @NamesTable nt
                 FOR XML PATH('')) AS NVARCHAR(MAX))
   )
SELECT SUBSTRING(CommaSeparatedXML, 2, LEN(CommaSeparatedXML))
FROM CommaSeparatedXML
GO

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked As Answer by perner123 Monday, January 28, 2013 1:48 PM
    •  
     
  • Monday, January 28, 2013 1:48 PM
     
     
    Sign In to Vote
    0
    that was quick! thx!
     
  • Monday, January 28, 2013 3:03 PM
     
      Has Code
    Sign In to Vote
    0
     
    SELECT STUFF((SELECT ',' + nt.Name
                 FROM @NamesTable nt
                 FOR XML PATH('')),1,1,'')

    Regards
    Satheesh

    Regards
    Satheesh

     
  • Monday, January 28, 2013 3:10 PM
     
     
    Sign In to Vote
    0
    is there also a possibility to "unstring" the variable?

    like: 
    INSERT INTO @mytable VALUES(@NamesTable)

    to insert a whole dataset with one variable? 
    like unstring variable, delimiter is ',' - something like this ;)