display the top 3 records in a comma separated in

Question

I came into problem that I need to display the top 3 records for each aId in a comma separated string in one column (for eg. aId=151 ghghg,ghh, rgtg ) instead of the below result. Can anyone help me please?

Expertise
  ghghg
  ghh
  rgtg
  rtrt
  ghgh
  tyuyu
  fgfg
  yuu
  dfdf
  gtyy
  dfdf
  df
  ssd
  dfd
  dfdf
  fd
  dfdf
  dd
  sdsds
  hghg
  hgh
  sdds
  dff
  rtrr
  fgfg
  bnbnb
  sdss

create table Expertise(
  Id                   bigint      not null identity constraint PK_Expertise primary key,
  aId                  bigint      not null,
  Expertise            varchar(25) not null,
  NoInMonthsExperience int,
  IsPrimary            bit,
  sId                  bigint      constraint DF_Expertise_sId default (0)
)
go

INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (62, 43, N'sds', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (63, 43, N'gg', 5, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (78, 151, N'ghghg', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (79, 151, N'ghh', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (80, 151, N'rgtg', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (81, 151, N'rtrt', 4, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (82, 151, N'ghgh', 5, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (83, 151, N'tyuyu', 6, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (84, 151, N'fgfg', 4, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (85, 151, N'yuu', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (86, 151, N'dfdf', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (87, 151, N'gtyy', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (88, 151, N'dfdf', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (89, 151, N'df', 3, 1, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (90, 151, N'ssd', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (91, 151, N'dfd', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (92, 151, N'dfdf', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (93, 151, N'fd', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (94, 151, N'dfdf', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (95, 151, N'dd', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (97, 151, N'sdsds', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (98, 151, N'hghg', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (99, 151, N'hgh', 5, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (100, 151, N'sdds', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (101, 151, N'dff', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (102, 151, N'rtrr', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (103, 151, N'fgfg', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (104, 151, N'bnbnb', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (105, 151, N'sdss', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (108, 153, N'hgh', 5, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (109, 153, N'ghg', 5, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (110, 153, N'554', 4, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (111, 153, N'ghg', 5, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (112, 153, N'fttr', 4, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (113, 154, N'.NEt', 5, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (114, 154, N'Java', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (115, 154, N'PHP', 3, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (151, 168, N'ghgh', 4, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (152, 168, N'sdssd', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (183, 156, N'909', 7, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (185, 156, N'tyty', 6, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (193, 185, N'asas', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (194, 184, N'.Net', 4, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (195, 184, N'Php', 4, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (196, 168, N'sdsd', 23, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (197, 168, N'wew12', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (198, 168, N'qwqw', 2, 0, 1)
INSERT [dbo].[Expertise] ([Id], [aId], [Expertise], [NoInMonthsExperience], [IsPrimary], [sId]) VALUES (199, 168, N'erer', 24, 0, 1)

Answer 1

I'm assuming the "top three" are the most frequently occuring three elements in the Expertise column, but this can be changed if its not what you meant.

WITH T1 AS (
    SELECT TOP(3) Expertise, COUNT(*) AS cnt
    FROM Expertise
    GROUP BY Expertise
    ORDER BY cnt DESC
), T2 AS (
   SELECT ROW_NUMBER() OVER (ORDER BY cnt DESC) AS rn, Expertise
   FROM T1
)
SELECT (SELECT Expertise FROM T2 WHERE rn = 1) + ',' + 
       (SELECT Expertise FROM T2 WHERE rn = 2) + ',' +
       (SELECT Expertise FROM T2 WHERE rn = 3)

Result:

dfdf,.NEt,fgfg

Answer 2

Nevermind I already got it, here is the answer

SELECT DISTINCT
aId, 
EmpNames = substring( ( SELECT Top 3 ', ' + Expertise
FROM Expertise e2 
WHERE e2.aId = e1.aId FOR XML path(''), elements  
),2,500) 
FROM Expertise e1

Answer 3

Use:

SELECT e.ald,
            STUFF(SELECT TOP 3 
                                              ', '+x.enterprise                
                                    FROM dbo.Enterprise x
                                  WHERE x.ald = e.ald
                                FOR XML PATH ('')),1,2,'')
  FROM ENTERPRISE e
GROUP BY e.ald