2

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?

2
  • What type of problem do you have with you query? Error message, incorrect result or something else? Mar 22, 2016 at 13:15
  • You can use the PIVOT function
    – BuahahaXD
    Mar 22, 2016 at 13:15

2 Answers 2

3

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
1

The test-bed setup (which you should always include in your question so we don't have to waste our time building one) is:

IF COALESCE(OBJECT_ID('dbo.StoreSales'), 0) <> 0
DROP TABLE dbo.StoreSales;

CREATE TABLE dbo.StoreSales
(
    StoreSalesID INT NOT NULL
        CONSTRAINT PK_StoreSales
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StoreNum INT NOT NULL
    , Notes VARCHAR(1000) NULL
);

TRUNCATE TABLE dbo.StoreSales;

INSERT INTO dbo.StoreSales (StoreNum, Notes)
VALUES (1463, NULL)
    , (1463, 'test')
    , (1638, 'some notes')
    , (1638, 'test')
    , (1638, NULL);

Your query:

SELECT
      COUNT(CASE WHEN storenum = '1463' AND CAST(notes As VARCHAR(MAX)) IS NOT NULL THEN StoreSalesID else NULL end) As [Passed w/ notes]
    , COUNT(CASE WHEN storenum = '1463' AND CAST(notes As VARCHAR(MAX)) IS NULL     THEN StoreSalesID else NULL end) As [Passed w/o notes]
    , COUNT(CASE WHEN storenum = '1638' AND CAST(notes As VARCHAR(MAX)) IS NOT NULL THEN StoreSalesID else NULL end) As [Passed w/ Notes]
    , COUNT(CASE WHEN storenum = '1638' AND CAST(notes As VARCHAR(MAX)) IS NULL     THEN StoreSalesID else NULL end) As [Passed w/o notes]
FROM dbo.StoreSales;

It's results:

enter image description here

The query, rewritten such that stores are listed vertically, with a summary for "with notes" and "without notes" as columns:

SELECT ss.StoreNum
    , [Passed w/ Notes]  = SUM(CASE WHEN notes IS NOT NULL THEN 1 ELSE 0 END)
    , [Passed w/o Notes] = SUM(CASE WHEN notes IS NULL     THEN 1 ELSE 0 END)
FROM dbo.StoreSales ss
GROUP BY ss.StoreNum;

The result:

enter image description here

There are several things about your original query that are potential red flags:

  1. Why are you casting a "Notes" column into a VARCHAR(MAX); is that column defined as Text or something? Be aware that (MAX) types can be very problematic if you don't truly need a very large variable - performance is generally better if you size columns and variables properly according to actual needs.

  2. You are using COUNT() with hard-coded store numbers, but not including those store numbers in your output, making the output difficult to decode by someone unfamiliar with the query itself.

  3. You appear to be treating the storenum column as text. If that column only ever can include numbers, why not store it as an INT? You'll save space and speed when indexing and querying results that include the store number.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.