SFMC SQL snippets

SQL to add comma to a Amount/Price field

SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,'12345678'),1), '.00','') 
AS [Amount]
FROM YourDEname

Output = 12,345,678
Details = Commas will be added after every 3 digit from right, 
          according to International numbering system  

Auto generate number series

SELECT Concat('MySeries_',_CustomObjectKey)
AS [Amount]
FROM YourDEname

Output:
MySeries_1
MySeries_2

Details = '_CustomObjectKey' is the field used generate the series. 

Comma seperated values --> Seperate rows

Existing Rows	Expected Rows
email Status vishal@yopmail.com gold,silver kumarc@yopmail.com gold,platinum	email Status vishal@yopmail.com gold vishal@yopmail.com silver kumarc@yopmail.com gold kumarc@yopmail.com platinum

 SELECT a.email,
       Substring(a.status, 1, Isnull(p1 - 1, Len(a.status))) AS [status]
FROM   [poc_split_based_on_comma] a
       CROSS apply (SELECT NULLIF(Charindex(',', a.status), 0)) b(p1)
       CROSS apply (SELECT NULLIF(Charindex(',', a.status, p1 + 1), 0)) c(p2)
UNION
SELECT a.email,
       Substring(a.status, p1 + 1, Isnull(p2, Len(a.status) + 1) - p1 - 1) AS
       [status2]
FROM   [poc_split_based_on_comma] a
       CROSS apply (SELECT NULLIF(Charindex(',', a.status), 0)) b(p1)
       CROSS apply (SELECT NULLIF(Charindex(',', a.status, p1 + 1), 0)) c(p2)