Column Alias name generation based on column values

Question

I need to generate alias based on column names as shown in below query. Please suggest how to achieve this. I will be using it inside a stored procedure.

select N.ipAddress,

(case when sid = 185 and M.rid = 13785 then M.avg else 0 end) as {R.name + M.name},
(case when sid = 187 and M.rid = 13753 then M.avg else 0 end) as {R.name + M.name}

from statsTable as M,ipTable as N, resTable as R where M.rid in (13784,13751,13745,13746,13753,13748,13785) and M.sid in (185,187)  and R.ipAddress = N.ipAddress and M.rid = R.rid and M.timestamp = 1463855400 group by N.ipAddress,M.sid

How do you expect to have a different column alias per row? Your question doesn't make sense. — Blorgbeard, May 24 '16 at 01:22

score 0 · Answer 1 · edited May 23 '17 at 12:24

I am assuming that the problem you have is that you will need inside the procedure this M.avg (or zero if it is the case) and also R.name + M.name.

If this is the case you will need two different columns, one for the value and another for the name, so this will be the way

select N.ipAddress,

(case when sid = 185 and M.rid = 13785 then M.avg else 0 end) as value,
 {R.name + M.name} as name

from statsTable as M,ipTable as N, resTable as R where M.rid in (13784,13751,13745,13746,13753,13748,13785) and M.sid in (185,187)  and R.ipAddress = N.ipAddress and M.rid = R.rid and M.timestamp = 1463855400 group by N.ipAddress,M.sid

I am not sure about the brackets in the sql, but I assume that is allowed by your sql engine.

with this approach you will be able to read it in a procedure.

If you need to transpose the result then you will need a pivot, this is a related post Simple way to transpose columns and rows in Sql?

Thanks for the reply. I did it in similar way using pivot. – praveen sinha Jun 03 '16 at 07:45 — praveen sinha, Jun 03 '16 at 07:45

Column Alias name generation based on column values

1 Answers1