locked
Result set in single quotes('') RRS feed

  • Question

  • hi all ,  hope below explanation will be enough , if you need any other i am happy to provide . any help will be appreciated. 

    I have a table ( testdata ) with following fields and data 

    ID     ID1     ID2      Name

    1     2         3        A

    1     2         3       B 

    4     5        6       D

    4     5        6       E

    below SQL is giving me the result as follows 

    SELECT  id,id1,id2
    ,fullname = substring((
    SELECT  ' ,'+ name
     FROM testdata m
     WHERE m.ID = m1.ID AND  m.ID1 = m1.ID1 and  m.ID2 = m1.ID2 
     FOR XML PATH('')),2),255)
     from [dbo].[testdata] m1
    group by id1,id2,id

    result set : 

    id      id1     id2      name 

    1         2      3          A,B 

    4         5     6          D,E 

    But i am looking for a result set as shown below

    id      id1     id2      name 

    1         2      3          'A','B'

    4         5     6          'D','E'

    Friday, October 10, 2014 2:48 PM

Answers

  • SELECT  id,id1,id2
     ,fullname = substring((
     SELECT  ' ,'''+ name + ''''
      FROM testdata m
      WHERE m.ID = m1.ID AND  m.ID1 = m1.ID1 and  m.ID2 = m1.ID2 
      FOR XML PATH('')),2),255)
      from [dbo].[testdata] m1
     group by id1,id2,id 


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Skuragayala Friday, October 10, 2014 3:03 PM
    Friday, October 10, 2014 2:52 PM
  • Two things.

    What you are asking for is the change of the SELECT line to

    SELECT  ' ,'''+ name + ''''

    The second thing: if you intend on using the result in Dynamic SQL or dynamically as code in any other language (such as JavaScript), then be aware that your code is vulnerable to code injection.


    Gert-Jan

    • Marked as answer by Skuragayala Friday, October 10, 2014 3:03 PM
    Friday, October 10, 2014 2:54 PM

  • SELECT  id,id1,id2
    ,fullname = substring((
    SELECT  '''' + name + ''' ,'
     FROM testdata m
     WHERE m.ID = m1.ID AND  m.ID1 = m1.ID1 and  m.ID2 = m1.ID2 
     FOR XML PATH('')),2),255)
     from [dbo].[testdata] m1
    group by id1,id2,id
    result will be liks 
    'xxxxxxxxx',
    • Marked as answer by Skuragayala Friday, October 10, 2014 3:03 PM
    Friday, October 10, 2014 2:57 PM

All replies

  • SELECT  id,id1,id2
     ,fullname = substring((
     SELECT  ' ,'''+ name + ''''
      FROM testdata m
      WHERE m.ID = m1.ID AND  m.ID1 = m1.ID1 and  m.ID2 = m1.ID2 
      FOR XML PATH('')),2),255)
      from [dbo].[testdata] m1
     group by id1,id2,id 


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Skuragayala Friday, October 10, 2014 3:03 PM
    Friday, October 10, 2014 2:52 PM
  • Two things.

    What you are asking for is the change of the SELECT line to

    SELECT  ' ,'''+ name + ''''

    The second thing: if you intend on using the result in Dynamic SQL or dynamically as code in any other language (such as JavaScript), then be aware that your code is vulnerable to code injection.


    Gert-Jan

    • Marked as answer by Skuragayala Friday, October 10, 2014 3:03 PM
    Friday, October 10, 2014 2:54 PM

  • SELECT  id,id1,id2
    ,fullname = substring((
    SELECT  '''' + name + ''' ,'
     FROM testdata m
     WHERE m.ID = m1.ID AND  m.ID1 = m1.ID1 and  m.ID2 = m1.ID2 
     FOR XML PATH('')),2),255)
     from [dbo].[testdata] m1
    group by id1,id2,id
    result will be liks 
    'xxxxxxxxx',
    • Marked as answer by Skuragayala Friday, October 10, 2014 3:03 PM
    Friday, October 10, 2014 2:57 PM