ansaurus

Question

display the top 3 records in a comma separated in one column

Answer 1

A:

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

2010-07-30 01:47:36

That doesn't seem to do what you asked for.

Mark Byers 2010-07-30 01:51:55

@Mark Byers - it will, it works when I executed it

2010-07-30 02:05:19

Answer 2

+1 A:

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

OMG Ponies 2010-07-30 01:47:40

@OMG Ponies - it throws an errors

2010-07-30 02:06:03

Answer 3

+1 A:

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

Mark Byers 2010-07-30 02:00:40

ansaurus

tags:

views:

answers:

display the top 3 records in a comma separated in one column

related questions