CODEWITHSUNDEEP
sqlsql-server
user5741399
user5741399

Reputation: 13

SQL pivot columns null (code/results included)

I have been playing with the SQL Fiddle, and am very close to being done - I just want some help on getting the column data returned ( I think the column varchar title with two words (example: High Value) is throwing it off.

This is query I'm struggling with:

SELECT Velocity
  , [Very High]
  , High
  , [Medium]
  , [Low]
  , [Very Low]
  , [Total]
FROM (
  SELECT CASE 
      WHEN GROUPING(velocity) = 0 THEN CAST(velocity AS CHAR(7))
      ELSE 'Total'
    END AS velocity
    , CASE 
      WHEN GROUPING(volume) = 0 THEN CAST(volume AS CHAR(7))
      ELSE 'Total'
    END AS volume
    , SUM(Sales) AS Sales
  FROM test1
  GROUP BY velocity, volume
  WITH CUBE
  ) AS s
PIVOT(SUM(Sales) FOR volume IN ([Very High], [High], [Medium], [Low], [Very Low], [Total])) AS p;

I'm expecting to see this result: Result wanted here:

Adding a SQL Fiddle

Upvotes: 1

Views: 73

Answers (1)

fabulaspb
fabulaspb

Reputation: 1263

I've found mistake in your SQL query. You need to change CAST(volume AS CHAR(7)) to CAST(volume AS CHAR(12)) Otherwise you have in your inner temp table values like "Very Hi" and "Very Lo".

Upvotes: 1

Related Questions