Sql-server – Show Query Results Vertical

sql serversql-server-2008-r2t-sql

I am wanting to show these results vertical as opposed to the default horizontal. My desired output is like this

Passed w/ notes 26
Passed w/o notes 28
Passed w/ notes 52
Passed w/o notes 14

Here is my query – how should I write this so that the results are displayed vertically?

Select
COUNT(case when storenum = '1463' AND CAST(notes As VARCHAR(MAX)) IS NOT NULL THEN id else NULL end) As [Passed w/ notes]
,COUNT(case when storenum = '1463' AND CAST(notes As VARCHAR(MAX)) IS NULL THEN id else NULL end) As [Passed w/o notes]
,COUNT(case when storenum = '1638' AND CAST(notes As VARCHAR(MAX)) IS NOT NULL   THEN id else NULL end) As [Passed w/ Notes]
,COUNT(case when storenum = '1638' AND CAST(notes As VARCHAR(MAX)) IS NULL THEN id else NULL end) As [Passed w/o notes]
FROM monster.dbo.storesales

I looked at using a PIVOT and from my readings syntax would be something PIVOT(SUM(ID) FOR fieldname IN ()

But I do not have a specific fieldname to use for the "FOR" clause of my pivot. How would I work around that?

Best Answer

You have transposed your desired result so it is rather straightforward to achieve what you want:

select storenum
     , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end as x
     , count(1)
from monster.dbo.storesales
group by storenum
    , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end

Now you can map each storenum / notes combination to a text, something like:

select case when storenum = '1463' and x = 0 then 'Passed w/ notes'
            when storenum = '1463' and x = 1 then ...
            when ...
     , cnt
from (
    select storenum
         , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end as x
     , count(1) as cnt
    from monster.dbo.storesales
    group by storenum
           , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end
) as T
Related Question