locked
Select problem RRS feed

  • Question

  • Hello,

    I have table that hac column name1 with the following data

    name1:

    a

    b

    c

    d

    e

    I want to get an output in a select like: a,b,c,d,e

    Is it possible somehow to make sum of nvarchar? How do I acomplish  a,b,c,d,e in my case?  lets assume that i can have unspecified number of records, not ony 5.

    Thanks

    Wednesday, April 4, 2012 9:13 AM

Answers

All replies

  • Hi,

    Please refer to trick mentioned in below post

    http://codecorner.galanter.net/2009/06/25/t-sql-string-aggregate-in-sql-server/


    - Chintak (My Blog)

    • Marked as answer by Barbi Rio Wednesday, April 4, 2012 10:09 AM
    Wednesday, April 4, 2012 9:16 AM
  • Barbi,

    Please refer 


    Thanks
    Manish

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

    • Marked as answer by Barbi Rio Wednesday, April 4, 2012 10:16 AM
    Wednesday, April 4, 2012 9:23 AM
  • Hello Barbi Rio,

    Try this

    SELECT STUFF(( SELECT ', ' + CAST(Col_Name AS VARCHAR)
    FROM Table_Name FOR XML PATH(''), ELEMENTS),1,2,N'') AS Alias_Name

    Thanks & Regards

    • Marked as answer by Barbi Rio Wednesday, April 4, 2012 10:08 AM
    Wednesday, April 4, 2012 9:24 AM
  • try this :

    declare @table table(name1 varchar(3))
    
    insert into @table
    select 'a'
    union
    select 'b'
    union
    select 'c'
    union
    select 'd'
    union
    select 'e'
    union
    select 'f'
    
    
    
    select STUFF ( (SELECT ',' + name1
    FROM @table 
    FOR XML PATH('')), 1, 1, '') AS name1

    • Marked as answer by Barbi Rio Wednesday, April 4, 2012 10:16 AM
    Wednesday, April 4, 2012 9:25 AM
  • If Object_Id('tempdb.dbo.#TmpTable') Is Not Null
    Begin
    Drop Table dbo.#TmpTable
    End


    Create Table dbo.#TmpTable(Item Varchar(10))


    Insert Into dbo.#TmpTable values(1)
    Insert Into dbo.#TmpTable values(2)
    Insert Into dbo.#TmpTable values(3)
    Insert Into dbo.#TmpTable values(4)
    Insert Into dbo.#TmpTable values(5)


    Select * From dbo.#TmpTable 




    Declare @String As Varchar(max)
    Select @String = ''


    Select @String =@String + Item +',' From dbo.#TmpTable


    Select Substring(@String,1,len(@String)-1) 
    Wednesday, April 4, 2012 9:52 AM
  • Hi Barbi,

    If you are trying to design a report then i suggest USE this field in column group other wise Pivot on dynamic columns is the choice.


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    Wednesday, April 4, 2012 1:14 PM
  • As you will see from the site below, there are many ways to do this:

    http://www.projectdmx.com/tsql/rowconcatenate.aspx#RecurCTE

    Pay attention to the part about 'The blackbox XML methods'


    Ryan Shuell


    • Edited by ryguy72 Wednesday, April 4, 2012 6:59 PM
    Wednesday, April 4, 2012 6:57 PM
  • Just stumbled across this; want to share it:

    http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/ 


    Ryan Shuell


    • Edited by Naomi N Friday, April 13, 2012 9:47 PM Link fix
    Wednesday, April 4, 2012 7:42 PM