Convert SQL Server result set into string

Question

I am getting the result in SQL Server as   SELECT StudentId FROM Student WHERE condition   xyz   I am getting the output like   StudentId 1236  7656  8990            The output parameter of the stored procedure is  studentId string and I want the return statement as   1236  7656  8990    How can I convert the output in the single string    I am returning single column  ie  StudentId

User · Answer

The answer from brad v is incorrect  It won t give you a concatenated string   Here s the correct code  almost like brad v s but with one important change   DECLARE  results VarChar 1000    SELECT  results   CASE      WHEN  results IS NULL THEN CONVERT  VarChar 20    StudentId        ELSE  results          CONVERT  VarChar 20    StudentId     END FROM Student WHERE condition   abc    See the difference     brad v please fix your answer  I can t do anything to correct it or comment on it  cause my reputation here is zero  I guess I can remove mine after you fix yours  Thanks

User · Answer

The following is a solution for MySQL  not SQL Server   i couldn t easily find a solution to this on stackoverflow for mysql  so i figured maybe this could help someone     ref  https   forums mysql com read php 10 285268 285286 msg-285286  original query      SELECT StudentId FROM Student WHERE condition   xyz  original result set     StudentId 1236 7656 8990   new query w  concat     SELECT group concat concat ws      StudentId  separator        FROM Student  WHERE condition   xyz   concat string result set     StudentId 1236  7656  8990   note  change the  separator  to whatever you would like  GLHF

User · Answer

Both answers are valid  but don t forget to initializate the value of the variable  by default is NULL and with T-SQL   NULL    Any text    gt  NULL   It s a very common mistake  don t forget it   Also is good idea to use ISNULL function   SELECT  result    result   ISNULL StudentId            FROM Student

User · Answer

Assign a value when declaring the variable  DECLARE  result VARCHAR 1000        SELECT  result   CAST StudentId AS VARCHAR        FROM Student WHERE condition   xyz

User · Answer

This one works with NULL Values in Table and doesn t require substring operation at the end  COALESCE is not really well working with NULL values in table  if they will be there    DECLARE  results VARCHAR 1000        SELECT  results    results               ISNULL CASE WHEN LEN  results    0 THEN    ELSE     END    StudentId       FROM Student WHERE condition   xyz  select  results

User · Answer

Use the COALESCE function    DECLARE  StudentID VARCHAR 1000  SELECT  StudentID   COALESCE  StudentID              StudentID FROM Student WHERE StudentID IS NOT NULL and Condition  XYZ  select  StudentID

User · Answer

Test this    DECLARE  result NVARCHAR MAX    SELECT  result   STUFF                              SELECT       CONVERT NVARCHAR 20   StudentId                               FROM Student                              WHERE condition   abc                              FOR xml path                                                         1                           1

User · Answer

Use the CONCAT function to avoid conversion errors   DECLARE  StudentID VARCHAR 1000  SELECT  StudentID   CONCAT COALESCE  StudentID             StudentID  FROM Student WHERE StudentID IS NOT NULL and Condition  XYZ  select  StudentID

User · Answer

DECLARE  result varchar 1000   SELECT  result   ISNULL  result        StudentId       FROM Student WHERE condition   xyz  select substring  result  0  len  result  - 1  --trim extra     at end

User · Answer

or a single select statement     DECLARE  results VarChar 1000  SELECT  results   CASE      WHEN  results IS NULL THEN CONVERT  VarChar 20    StudentId        ELSE        CONVERT  VarChar 20    StudentId      END FROM Student WHERE condition   abc

[sql] Convert SQL Server result set into string

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql