CODEWITHSUNDEEP
sql-serverpivot
user2603688
user2603688

Reputation: 171

converting column result to row result in sql

I have query like this:

SELECT v.Vtype,
       SUM(DATEDIFF(MI, t.Paydate, t.DelDate)) AS TotalDiff,
       CONVERT(DECIMAL(10, 1), 
               AVG(CONVERT(NUMERIC(18, 2), DATEDIFF(MI, t.Paydate, t.DelDate)))) AS Average
FROM   Transaction_tbl t
       LEFT JOIN VType_tbl v
         ON t.vtid = v.vtid
WHERE  t.Locid = 5
GROUP  BY v.Vtype

Average from Transaction_tbl t left join VType_tbl v on t.vtid=v.vtid where t.Locid =5 group by v.Vtype

My output:

Vtype                         TotalDiff   Average
-------------------------------------------------- -
Emaar Staff                     NULL        NULL
Normal                         14044       189.8
VIP                             85          2.1
VVIP                               5           2.5

but i want to get out like this:

VtypeE      Etotaldiff       Eaverage  VtypeN  Ntot   Naverge  vtypev  vtot  vaverg
Emaar Staff  null             null     Normal   14044    189.8   VIP     85       2.1

Upvotes: 0

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You are trying to bring the results all back on one row. But, the columns have different types. That means that the built in pivot is less practical. You can do what you want by explicitly aggregating your original query:

with cte as (
      SELECT v.Vtype, SUM(DATEDIFF(MI, t.Paydate, t.DelDate)) AS TotalDiff,
            CONVERT(DECIMAL(10, 1), 
            AVG(CONVERT(NUMERIC(18, 2), DATEDIFF(MI, t.Paydate, t.DelDate)))) AS Average
      FROM   Transaction_tbl t LEFT JOIN
             VType_tbl v
             ON t.vtid = v.vtid
     WHERE  t.Locid = 5
     GROUP  BY v.Vtype 
   )
select 'Emaar Staff' as VtypeE,
       max(case when type = 'Emaar Staff' then TotalDiff end) as ETotalDiff,
       max(case when type = 'Emaar Staff' then Average end) as EAverage,
       'Normal' as VtypeN,
       max(case when type = 'Normal' then TotalDiff end) as NTotalDiff,
       max(case when type = 'Normal' then Average end) as NAverage,
       'VIP' as VtypeV,
       max(case when type = 'VIP' then TotalDiff end) as VTotalDiff,
       max(case when type = 'VIP' then Average end) as VAverage
from cte;

Upvotes: 1

Related Questions