CODEWITHSUNDEEP
sqlsql-server
eikaz
eikaz

Reputation: 401

SQL Server : how to get top 5 data from 4 Column

Need your help on this. Below is my table of data:

enter image description here

As you can see above picture, (F)Atekizu x4, (B)Dense x3, (B)DIP Stain x2, (F)D-Scratch x2, (F)Other Stain x2 ....

I want to get Top 5 of which most defect have been hit the surface. Below is the example of the output that I need to show:

enter image description here

Please help. Thank you.

I use below solutions to get the answer as below:

enter image description here

But can you help to pivot it? I try but the result I got is:

enter image description here

Below are my syntax to create the pivot:

SELECT [1], [2], [3], [4], [5]
FROM
(
    SELECT top 5 ROW_NUMBER() OVER (ORDER BY count(*) desc)as rowNo,
    vals.val, count(*) as countt
    from abcd outer apply
         (values (a), (b), (c), (d)) vals(val)
    where val is not null
    group by vals.val
    --order by count(*) desc
) source
pivot
(
MAX(val)
for rowNo in ([1],[2],[3],[4],[5])
) piv

Please help.

Thank you.

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I think you want to unpivot the data and then aggregate:

select top 5 vals.val, count(*)
from t outer apply
     (values (a), (b), (c), (d)) vals(val)
group by vals.val
order by count(*) desc;

This puts the top 5 into separate rows rather than separate columns.

EDIT:

There are multiple ways to pivot the result. I generally go for conditional aggregation:

with cte as (
      select top 5 vals.val, row_number() over (order by count(*) desc) as seqnum
      from t outer apply
           (values (a), (b), (c), (d)) vals(val)
      group by vals.val
      order by count(*) desc
     )
select max(case when seqnum = 1 then val end) as val1,
       max(case when seqnum = 2 then val end) as val2,
       max(case when seqnum = 3 then val end) as val3,
       max(case when seqnum = 4 then val end) as val4,
       max(case when seqnum = 5 then val end) as val5
from cte;

Upvotes: 4

Felix Pamittan
Felix Pamittan

Reputation: 31879

Adding to Gordon's excellent answer, you need to use ROW_NUMBER to determine the ranking and use conditional aggregation to pivot the result:

WITH Cte AS(
    SELECT *, rn = ROW_NUMBER() OVER (ORDER BY cnt DESC)
    FROM (
        SELECT
            x.val, cnt = COUNT(*)
        FROM yourTable t
        CROSS APPLY(VALUES
            (a), (b), (c), (d)
        ) x(val)
        GROUP BY x.val
    ) t
    WHERE rn <= 5
)
SELECT
    [1] = MAX(CASE WHEN rn = 1 THEN val END),
    [2] = MAX(CASE WHEN rn = 2 THEN val END),
    [3] = MAX(CASE WHEN rn = 3 THEN val END),
    [4] = MAX(CASE WHEN rn = 4 THEN val END),
    [5] = MAX(CASE WHEN rn = 5 THEN val END)
FROM Cte

Upvotes: 1

Related Questions