Asd

Monday, May 2, 2011

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

No comments:

Post a Comment