To get a comma separated list in SQL & MDX.

To get a comma separated list in SQL as well as in MDX use below queries.

1 -  SQL

Select Name from Sales.SalesReason

GO

Select Name from Sales.SalesReason

SELECT

      SUBSTRING

      (

                  (

                  SELECT

                        ',' + s.Name

                  FROM

                        Sales.SalesReason  s

                  ORDER BY

                        s.Name

                  FOR XML PATH('')

                  )

      ,2,200000 ) AS NAME

2 - MDX

WITH

SET [COMMA] AS [Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS

MEMBER [MEASURES].[COMMATEST] AS

    Generate

    (

      [COMMA]

     ,[COMMA].CurrentMember.Name

     ,","

    )

SELECT

NON EMPTY

      {

      [MEASURES].[COMMATEST]

      } ON 0

FROM

      [ADVENTURE WORKS];

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2