Results 1 to 6 of 6

Thread: cross tab query

  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: cross tab query

    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
    123 555

    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.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    Thanks for the response. Is this for SQL Server 2000?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The point I was trying to make is you shouldn't be doing this in SQL Server. How are you displaying the information?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2008
    Posts
    135
    try this
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •