T-SQL Rows to column and compute

Question

Need a little help converting this:

to...

where

Percentage is simply N / (D - E)

I did read this, this and few other posts.
Is there a simpler way to add compute to the transposed columns !?

score 3 · Accepted Answer · answered Sep 24 '19 at 19:48

3

Just another option

Select *
      ,Pct = ( IsNull([N],0)+0.0) / NullIf((IsNull([D],0)-IsNull([E],0) ),0)
 From  YourTable A
 Pivot ( sum(Users) for Marker in ([D],[E],[N]) ) pvt

EDIT - Corrected for NULLS

answered Sep 24 '19 at 19:48

John Cappelletti

79,615
7
44
66

1

I was feeling a little dumb to ask a SQL question, but thanks a ton for helping me out. – WeShall Sep 24 '19 at 19:57
2

@WeShall No worries, that's why we are here :) Learning is the fun part – John Cappelletti Sep 24 '19 at 19:57

score 1 · Answer 2 · answered Sep 24 '19 at 18:56

1

I would use a subquery or CTE:

select d.*,
       n * 1.0 / nullif(d - e, 0) as ratio
from (select date,
             sum(case when market = 'D' then users else 0 end) as d,
             sum(case when market = 'E' then users else 0 end) as e,
             sum(case when market = 'N' then users else 0 end) as n
      from t
      group by date
     ) d;

answered Sep 24 '19 at 18:56

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon. I referred to some of your previous answers / comments on the topic. That was helpful too. – WeShall Sep 24 '19 at 19:58

T-SQL Rows to column and compute

2 Answers2