This is a slightly long approach.....but this is one way you can do it :

;With CTE

As

(

Select 1 As Number Union Select 2Union Select 3 Union Select 4

),

CTE1

As

(

Select Cast(Number As Varchar) As N1, '' As N2, '' As N3, '' As N4 From CTE

Union ALL

Select DISTINCT Cast(a.Number As Varchar), Cast(b.Number As Varchar), '', '' From CTE As a CROSS JOIN CTE As b Where b.Number > a.Number

Union ALL

Select DISTINCT Cast(a.Number As Varchar), Cast(b.Number As Varchar), Cast(c.Number As Varchar), ''

From CTE As a CROSS JOIN CTE As b CROSS JOIN CTE As c

Where b.Number > a.Number AND c.Number > b.Number

Union ALL

Select DISTINCT Cast(a.Number As Varchar), Cast(b.Number As Varchar), Cast(c.Number As Varchar), Cast(d.Number As Varchar)

From CTE As a CROSS JOIN CTE As b CROSS JOIN CTE As c CROSS JOIN CTE As d

Where b.Number > a.Number AND c.Number > b.Number AND d.Number > c.Number

)

Select DISTINCT STUFF((Select ';' + N1 From CTE1 Where N2 = '' AND N3 = '' AND N4 = '' AND N1 <> '' FOR XML PATH('')),1,1,'') From CTE1

Where N2 = '' AND N3 = '' AND N4 = '' AND N1 <> ''

UNION ALL

Select DISTINCT STUFF((Select ';(' + N1 + ',' + N2 + ')' From CTE1 Where N3 = '' AND N4 = ''AND N1 <> '' AND N2 <>'' FOR XML PATH('')),1,1,'')

From CTE1 Where N3 = '' AND N4 = ''AND N1 <> '' AND N2 <>''

UNION ALL

Select DISTINCT STUFF((Select ';(' + N1 + ',' + N2 + ',' + N3 + ')' From CTE1 Where N4 = ''AND N1 <> '' AND N2 <>'' AND N3 <> '' FOR XML PATH('')),1,1,'')

From CTE1 Where N4 = ''AND N1 <> '' AND N2 <>'' AND N3 <> ''

UNION ALL

Select DISTINCT STUFF((Select ';(' + N1 + ',' + N2 + ',' + N3 + ',' + N4 + ')' From CTE1 Where N4 <> ''AND N1 <> '' AND N2 <>'' AND N3 <> '' FOR XML PATH('')),1,1,'')

From CTE1 Where N4 <> ''AND N1 <> '' AND N2 <>'' AND N3 <> ''

Let me see if I can better this solution with a better approach.

Hope this helps. 🙂