Equivalent of Group_Concat function in MS-SQL

--Query to create the table given in the example
CREATE TABLE [dbo].[categoryproductlink](
[categoryproductlinkid] [int] IDENTITY(1,1) NOT NULL,
[categoryid] [int] NOT NULL,
[productid] [int] NOT NULL,
[endeffdt] [smalldatetime] NULL,
CONSTRAINT [PK_categoryproductlink] PRIMARY KEY CLUSTERED
(
[categoryproductlinkid] ASC
)) ON [PRIMARY]

GO
--Query to insert data in above table
SET IDENTITY_INSERT [dbo].[categoryproductlink] ON
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (1, 1, 1, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (2, 1, 2, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (3, 1, 3, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (4, 2, 4, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (5, 2, 5, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (6, 2, 6, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (7, 3, 2, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (8, 3, 6, NULL)
SET IDENTITY_INSERT [dbo].[categoryproductlink] OFF

Query 1 – Equivalent to Group_Concate function in MySQL
_________________________________________________________
SELECT CAST(productid AS varchar(20)) + ', ' AS [text()]
FROM categoryproductlink
WHERE categoryid = 2
FOR XML PATH('')
_________________________________________________________

Query 2 – Equivalent to Group_Concate function in MySQL
_________________________________________________________
DECLARE @prodidstr VARCHAR(MAX)

SELECT @prodidstr = COALESCE(@prodidstr+', ','')+CAST(productid AS varchar(8))
FROM categoryproductlink
WHERE categoryid = 2

SELECT @prodidstr
_________________________________________________________