I need to create a query to create my columns dynamically as employee names and their case id numbers (multiple numbers per employee). Do I us sub queries? What is the best way to do that? The result should look something like this:
John Jane Fred
222 333 444
111 223 224
I tried the query below but I keep getting null's in my result. I even tried using "isnull(max(case when employeeid = 1 then postalcode end ) , 0) as jane" in the select statement but I either get 0's or null instead of just the postalcode excluding the non-postalcodes. How do I eliminate the null/0's and just return the postalcode? I use a group by postalcode. Should I use something else?
select isnull(max(case when e.employeeid = 1 then a.postalcode end ) , 0) as fuller,
isnull(max(case when e.employeeid = 2 then a.postalcode end ) , 0)as Leverling,
isnull(max(case when e.employeeid = 3 then a.postalcode end ) , 0) as Peacock
FROM Employees e inner join address a on e.employeeid=a.employeeid
Group by a.postalcode
Should I use this method of do something else? Any help you could give would be great.
declare @tab table(Name varchar(32),ID int)
insert into @tab select
'John ', 1 union all select
'John ', 2 union all select
'John ', 3 union all select
'Fred ', 3 union all select
'Fred ', 2 union all select
'Jane ', 2 union all select
'Jane ', 3
select max(case when name = 'john' then id end) as john,
max(case when name = 'fred' then id end) as fred,
max(case when name = 'jane' then id end) as jane
from (select *,(select count(1) from @tab where name = t.name and id <=t.id )as row from @tab t)a
group by row
That's hardly dynamic.
GMAN1766, there are techniques to do this by referencing the system tables and constructing dynamic SQL, but Pootle is correct in that this is a presentation issue and has no business being handled in the database.
Let your application or reporting software format that data for you.
If it's not practically useful, then it's practically useless.