SQL Server convert select a column and convert it to a string

Question

Is it possible to write a statement that selects a column from a table and converts the results to a string   Ideally I would want to have comma separated values   For example  say that the SELECT statement looks something like  SELECT column FROM table WHERE column lt 10   and the result is a column with values   column  --------    1        3        5        9       I want as a result the string  1  3  5  9

User · Accepted Answer

You can do it like this   Fiddle demo  declare  results varchar 500   select  results   coalesce  results               convert varchar 12  col  from t order by col  select  results as results    RESULTS   -----------   1 3 5 9

User · Answer

select  stuff list 1 1     from              select        cast col1 as varchar 16   as  text            from    YourTable         for     xml path               as Sub list    Example at SQL Fiddle

User · Answer

------ ----------------------    type          names            ------ ----------------------    cat    Felon                    cat    Purz                     dog    Fido                     dog    Beethoven                dog    Buddy                    bird   Tweety                  ------ ----------------------    select group concat name  from Pets group by type  Here you can easily get the answer in single SQL and by using group by in your SQL you can separate the result based on that column value  Also you can use your own custom separator for splitting values Result   ------ ----------------------    type          names            ------ ----------------------    cat    Felon Purz               dog    Fido Beethoven Buddy     bird   Tweety                  ------ ----------------------

User · Answer

You can use the following method   select STUFF                    select        CONVERT varchar 10   ID  FROM  temp         where ID lt 50 group by ID for xml path                1  2      as IDs     Implementation   Declare  temp Table  ID int   insert into  temp  ID  values  1  insert into  temp  ID  values  3  insert into  temp  ID  values  5  insert into  temp  ID  values  9    select STUFF                    select        CONVERT varchar 10   ID  FROM  temp         where ID lt 50 group by ID for xml path                1  2      as IDs   Result will be

User · Answer

This a stab at creating a reusable column to comma separated string   In this case  I only one strings that have values and I do not want empty strings or nulls   First I create a user defined type that is a one column table   --                                  -- Create User-defined Table Type --                                  USE  RSINET MVC  GO  -- Create the data type CREATE TYPE  dbo   SingleVarcharColumn  AS TABLE        data NVARCHAR max    GO   The real purpose of the type is to simplify creating a scalar function to put the column into comma separated values   --                                                  -- Template generated from Template Explorer using  -- Create Scalar Function  New Menu  SQL -- -- Use the Specify Values for Template Parameters  -- command  Ctrl-Shift-M  to fill in the parameter  -- values below  -- -- This block of comments will not be included in -- the definition of the function  --                                                  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO --                                               -- Author   Rob Peterson         -- Create date  8-26-2015 -- Description  This will take a single varchar column and convert it to -- comma separated values  --                                               CREATE FUNCTION fnGetCommaSeparatedString        -- Add the parameters for the function here      column AS  dbo   SingleVarcharColumn  READONLY   RETURNS VARCHAR max  AS BEGIN -- Declare the return variable here DECLARE  result VARCHAR MAX  DECLARE  current VARCHAR MAX  DECLARE  counter INT DECLARE  c CURSOR  SET  result      SET  counter   0 -- Add the T-SQL statements to compute the return value here SET  c   CURSOR FAST FORWARD     FOR SELECT COALESCE data     FROM  column     OPEN  c     FETCH NEXT FROM  c     INTO  current     WHILE   FETCH STATUS   0     BEGIN         IF  result  lt  gt     AND  current  lt  gt     SET  result    result          current         IF  result      AND  current  lt  gt     SET  result    current     FETCH NEXT FROM  c     INTO  current     END     CLOSE  c     DEALLOCATE  c -- Return the result of the function RETURN  result  END GO   Now  to use this  I select the column I want to convert to a comma separated string into the SingleVarcharColumn Type   DECLARE  s as SingleVarcharColumn  INSERT INTO  s VALUES   rob   INSERT INTO  s VALUES   paul   INSERT INTO  s VALUES   james   INSERT INTO  s VALUES  null    INSERT INTO  s SELECT iClientID FROM  dbo  tClient  SELECT  dbo  fnGetCommaSeparatedString  s    To get results like this   rob paul james 1 9 10 11 12 13 14 15 16 18 19 23 26 27 28 29 30 31 32 34 35 36 37 38 39 40 41 42 44 45 46 47 48 49 50 52 53 54 56 57 59 60 61 62 63 64 65 66 67 68 69 70 71 72 74 75 76 77 78 81 82 83 84 87 88 90 91 92 93 94 98 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 120 121 122 123 124 125 126 127 128 129 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159  I made my data column in my SingleVarcharColumn type an NVARCHAR MAX  which may hurt performance  but I flexibility was what I was looking for and it runs fast enough for my purposes   It would probably be faster if it were a varchar and if it had a fixed and smaller width  but I have not tested it

User · Answer

Use simplest way of doing this-  SELECT GROUP CONCAT Column  from table

User · Answer

There is new method in SQL Server 2017   SELECT STRING AGG  column       AS column FROM Table   that will produce 1 3 5 9 for you

User · Answer

Use LISTAGG function  ex  SELECT LISTAGG colmn  FROM table name

User · Answer

SELECT  CAST  lt COLUMN Name gt  AS VARCHAR 3         FROM     lt TABLE Name gt  FOR     XML PATH

User · Answer

ALTER PROCEDURE  dbo   spConvertir CampoACadena    nomb tabla   varchar 30                              campo tabla  varchar 30                              delimitador  varchar 5                              respuesta    varchar max  OUTPUT   AS DECLARE  query      varchar 1000        cadena     varchar 500  BEGIN   SET  query    SELECT  cadena    COALESCE  cadena         delimitador                           campo tabla     FROM    nomb tabla   --select  query   EXEC  query    SET  respuesta    cadena   END

User · Answer

The current accepted answer doesn t work for multiple groupings  Try this when you need to operate on categories of column row-values   Suppose I have the following data    --------- -----------    column1    column2     --------- -----------    cat       Felon         cat       Purz          dog       Fido          dog       Beethoven     dog       Buddy         bird      Tweety       --------- -----------    And I want this as my output    ------ ----------------------    type          names            ------ ----------------------    cat    Felon Purz               dog    Fido Beethoven Buddy     bird   Tweety                  ------ ----------------------     If you re following along   create table  column to list  column1 varchar 30   column2 varchar 30   insert into  column to list values    cat   Felon      cat   Purz      dog   Fido      dog   Beethoven      dog   Buddy      bird   Tweety        Now     I don   t want to go into all the syntax  but as you can see  this does the initial trick for us   select       cast column2 as varchar 255   as  text      from  column to list sub where column1    dog  for xml path     --Using  as  text     here is specific to the    for XML    line after our where clause and we can   t give a name to our selection  hence the weird column name   output    ------------------------------------------    XML F52E2B61-18A1-11d1-B105-00805F49916B    ------------------------------------------     Fido Beethoven Buddy                       ------------------------------------------    You can see it   s limited in that it was for just one grouping  where column1      dog     and it left a comma in the front  and additionally it   s named weird   So  first let s handle the leading comma using the  stuff  function and name our column stuff list   select stuff  list  1 1     as stuff list from  select       cast column2 as varchar 255   as  text             from  column to list sub          where column1    dog           for xml path                sub query  list    -- sub query  list    just names our column as   list   so we can refer to it in the stuff function      Output    ----------------------         stuff list         ----------------------    Fido Beethoven Buddy    ----------------------    Finally let   s just mush this into a select statement  noting the reference to the top query alias defining which column1 we want  on the 5th line here    select top query column1              select stuff  list  1 1     as stuff list          from  select       cast column2 as varchar 255   as  text                      from  column to list sub                   where sub column1   top query column1                   for xml path                         sub query  list                   as pet list from   column to list top query group by column1 order by column1   output    --------- ----------------------    column1         pet list          --------- ----------------------    bird      Tweety                   cat       Felon Purz               dog       Fido Beethoven Buddy    --------- ----------------------    And we   re done   You can read more here       FOR XML PATH in SQL server and  text    https   docs microsoft com en-us sql relational-databases xml use-path-mode-with-for-xml view sql-server-2017 https   www codeproject com Articles 691102 String-Aggregation-in-the-World-of-SQL-Server

[sql] SQL Server convert select a column and convert it to a string

Examples related to sql

Examples related to sql-server

Examples related to string

Examples related to select