2

I would like to retrieve the a list of column names in one single string.

Every column name should appear as separated by a comma

Example : 

col1, col2, col3
Up_One
  • 5,213
  • 3
  • 33
  • 65
Revious
  • 7,816
  • 31
  • 98
  • 147

2 Answers2

2

Try like this

Select Stuff(
        (
        Select ', ' + T2.ColVlaue  // Add a comma (,) before each value
        From MyTable As T2
        Where T2.ID= T1.ID
        For Xml Path(''), type // Select it as XML
        ).value('.', 'nvarchar(max)'), 1, 2, '')  // This is done to remove the first character (,) from the result
From MyTable As T1
Group By T1.Id

Or:

DECLARE @Value VARCHAR(8000)  
SELECT @Value = COALESCE(@Names + ', ', '') + ColValue FROM People
SELECT @Value
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • Can you explain a bit how it works? Xml Path is a keyword? – Revious Mar 17 '14 at 15:01
  • 1
    @Revious A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries... The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner. – Vignesh Kumar A Mar 17 '14 at 15:02
  • Thanks, and what's the meaning of "Path" and ".value"? – Revious Mar 17 '14 at 15:05
  • 1
    I had put comment in my answer.please check it – Vignesh Kumar A Mar 17 '14 at 15:06
  • Nice solution, but I thought the OP was looking for column names, not values. Did I miss something? – Dave Mason Mar 17 '14 at 15:08
  • 1
    @DMason: ah, interesting solution.. it would be worth of modifiyng the text of the question.. what is the proposed solution exactly doing? – Revious Mar 17 '14 at 15:36
0

In ORACLE you can do this using the LISTAGG function:

    SELECT  
LISTAGG(COLUMN_NAME, ', ')
 WITHIN GROUP (ORDER BY COLUMN_NAME) "col names"
    FROM USER_TAB_COLUMNS
      where TABLE_NAME='Table names here';

Was i to late ? kkk :)

Up_One
  • 5,213
  • 3
  • 33
  • 65