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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote 

    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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote 

    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