CODEWITHSUNDEEP
sqlsql-server-2008
Olivarsham
Olivarsham

Reputation: 1731

Get the database table values in matrix format

I have a table tblInput with two col1 and col2.
I need to get the following output through a query.

enter image description here

So far i ve done this: 

select col1 + col2 from tblInput order by col1,col2

but this giving the ouput in single column. I want it in a matrix.
How to do it?

Upvotes: 0

Views: 1005

Answers (2)

Art
Art

Reputation: 5792

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
      , sum(sal)                   TOTAL
     FROM scott.emp
   GROUP BY job)
 ORDER BY 1
 /

 JOB           DEPT10     DEPT20     DEPT30     DEPT40      TOTAL
--------- ---------- ---------- ---------- ---------- ----------
ANALYST                    6000                             6000
CLERK           1300       1900        950                  4150
MANAGER         2450       2975       2850                  8275
PRESIDENT       5000                                        5000
SALESMAN                              5600                  5600

Upvotes: 1

AzizSM
AzizSM

Reputation: 6289

You need to use PIVOT in your query. Please refer to example at :

http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

Upvotes: 2

Related Questions