CODEWITHSUNDEEP
sqlsql-serverpivot
WillardSolutions
WillardSolutions

Reputation: 2314

Find highest count totals via PIVOT?

I have a large dataset wherein I need to count "Yes" responses for each question. My query looks like this:

SELECT 
SUM(CASE (question1) WHEN 'Y' THEN 1 ELSE 0 END) as question1
, SUM(CASE (question2) WHEN 'Y' THEN 1 ELSE 0 END) as question2
, SUM(CASE (question3) WHEN 'Y' THEN 1 ELSE 0 END) as question3
, SUM(CASE (question4) WHEN 'Y' THEN 1 ELSE 0 END) as question4
, SUM(CASE (question5) WHEN 'Y' THEN 1 ELSE 0 END) as question5
...... ( + dozens more ) ...
FROM myTable

This gives me a table like this: 

question1 | question2 | question3 | question4 | question5 | ...
-------------------------------------------------------------------
346       | 187       | 240       | 256       | 261       | ...

Fine. But I need to find the highest 3 values in this row. I've researched the PIVOT function in an attempt to transform my table to look like this:

name      | value
--------------------
question1 | 346
question2 | 187 
question3 | 240
question4 | 256
question5 | 261

but I frankly don't understand how the FOR ... IN section of the example code works, and moreover, how I'm supposed to list a column "that contains values that will become column headers", as the documentation explains:

PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>

Since I have a table with only 1 row, I don't have a column that will become column headers. Can anyone help me understand how to this works with my 1 row dataset - or better yet, a different way of approaching this? I feel like I may be missing something simpler. Again, the end goal is to find the 3 questions with the highest totals.

Upvotes: 0

Views: 59

Answers (2)

Lamak
Lamak

Reputation: 70648

You don't need PIVOT, you need to use UNPIVOT if you want to get those results. There are other ways to do this too, for instance, you can use CROSS APPLY:

;WITH CTE AS
(
SELECT  SUM(CASE (question1) WHEN 'Y' THEN 1 ELSE 0 END) as question1
        , SUM(CASE (question2) WHEN 'Y' THEN 1 ELSE 0 END) as question2
        , SUM(CASE (question3) WHEN 'Y' THEN 1 ELSE 0 END) as question3
        , SUM(CASE (question4) WHEN 'Y' THEN 1 ELSE 0 END) as question4
        , SUM(CASE (question5) WHEN 'Y' THEN 1 ELSE 0 END) as question5
        ...... ( + dozens more ) ...
FROM myTable
)
SELECT x.[name], x.Value
FROM CTE t
CROSS APPLY 
(
    VALUES
        ('question1', t.question1),
        ('question2', t.question2),
        ....
        ('questionN', t.questionN)
) x ([name], Value);

Upvotes: 2

FpontoDesenv
FpontoDesenv

Reputation: 162

I guess this can help you:

CREATE TABLE #X(ID INT IDENTITY,question1 INT,question2 INT,question3 INT ,question4 INT ,question5 INT)


INSERT INTO #X
select 346,187,240,256,261  


select top 3 *
from (
select 
id,
question,
[value]
from #X
unpivot([value]  for question in(question1,question2,question3,question4,question5))P
)X 
order by [value] desc

Upvotes: 0

Related Questions