Post reply

crosstab query

  • October 5, 2005 at 4:11 am

    #87959

    How can i make crosstab report through one query in sql server, in oracle it is just like this

     SELECT  * FROM  (SELECT job,

    sum(decode(deptno,10,sal)) DEPT10,

    sum(decode(deptno,20,sal)) DEPT20,

    sum(decode(deptno,30,sal)) DEPT30,

    sum(decode(deptno,40,sal)) DEPT40

    FROM scott.emp GROUP BY job) ORDER BY 1;

    Thanx

  • October 5, 2005 at 6:05 am

    #595101

    I don't know what decode does, and I don't know exactly what you're trying to do, but have a look at the following and see if it helps.

    SELECT job,

     sum(CASE deptno WHEN 10 then sal else 0 end) AS DEPT10,

     sum(CASE deptno WHEN 20 then sal else 0 end) AS DEPT20,

     sum(CASE deptno WHEN 30 then sal else 0 end) AS DEPT30,

     sum(CASE deptno WHEN 40 then sal else 0 end) AS DEPT40

    FROM emp

    GROUP BY job

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply