SQL Using a PIVOT to Convert row data to column

Question

Post reply

SQL Using a PIVOT to Convert row data to column

gjoelson 29755

SSC Eights!

Points: 848

August 1, 2022 at 12:44 pm

#4070896

Hello,

Im trying to get a Pivot to work , but its retuning a NULL . I tried various thing , like using Partition with Row_number as well , the below is the scipt but return null.

Basically, I'm looking for the Meshtype to be the column i.e Fine, coarse and Dry to be the columns

Currently, I have it like this:

pivot1

However, I would like it to display like this:

pivot

---- Total Tons produced Today-------
select meshtype,tonsperhour
from
(select 
sum(tonsperhour)TonsProduced,

Case when meshtype = 'Raw Infeed Fines' then 'Fines' 
when meshtype = 'Raw Infeed Coarse' then 'Course' 
when (meshtype IS NULL) then 'Dry1' ELSE 'Dry'
end as meshtype1
--,ROW_NUMBER() over (partition by meshtype order by meshtype) colseq

--sum(tonsperhour) TonsProduced
--, Case when meshtype = 'Raw Infeed Fines' then 'Fines' 
--when meshtype = 'Raw Infeed Coarse' then 'Course' 
--when (meshtype IS NULL) then 'Dry1' ELSE 'Dry'
--end as meshtype


from tons_per_hour
where 
datetime >= dateadd(day, datediff(day, 0, dbo.GetDateCDT(GETDATE())), 0)
 and datepart(mi, datetime)in (0) 
 and datepart(ss, datetime)=0 
 and datediff(d, datetime, getdate()) <=60
 and meshtype <> '100 mesh'
 and meshtype <> 'Raw Sand A'
 and meshtype <> 'Raw Sand B'
 and plant like '%san%' 
 --and meshtype  not in ( '40/140','40/150', '40/70', '50/140','Non frac', 'Washed Sand A', 'Washed Sand B', 'Washed Sand C','Waste' )
 and meshtype  not in ( 'Non frac', 'Washed Sand A', 'Washed Sand B', 'Washed Sand C','Waste' )
and meshtype  not in ('Non frac')
   
group by

meshtype ) as source

Pivot
(max(TonsProduced)
for
Meshtype1 in ([meshtype],[tonsperhour])
) as Piv1

thanks for the help.

September 1, 2022 at 1:04 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Ok... so tell us how you got the numbers is the "after" graphic from the numbers in the "currently have" graphic. The don't add up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 2

You PIVOT to turn row values into column names.

SQL Server does not provide dynamic PIVOT, you must specify the names for the new columns you want returned.

Because 'Dry', 'Fine' ('Fines' in the script) and 'Coarse' are the values on which you are pivoting, they are the column names in the pivoted results and must be explicitly provided in the SELECT and PIVOT clauses.

-- note: the image provided shows 'Fine' as the column name, but the script uses 'Fines'
-- ex: Case when meshtype = 'Raw Infeed Fines' then 'Fines' 

---- Total Tons produced Today-------
-- Note: Change the next line:
--     select meshtype,tonsperhour
--   To:
SELECT [Dry], [Fines], [Coarse]

from
(select 
sum(tonsperhour)TonsProduced,

[...snip...]

Pivot
(max(TonsProduced)
for
-- Note: change the following line so you PIVOT the values into column names
Meshtype1 in ([Dry], [Fines], [Coarse])
) as Piv1

Eddie Wuerch
MCM: SQL

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 3

Eddie, that helped big time - thank you.

Pivot is working well, Initially the sum of the Dry column seems to only pick up the first value for dry. I looked through query and I was using Max , changed it sum and vola

Thanks again !