CODEWITHSUNDEEP
sqlsql-server
Shibin V.M
Shibin V.M

Reputation: 431

Swap rows and columns of a table in sql server select

Denomination                     | Count
----------------------------------------
Cnanaya Catholic                 | 17
RC Latin Catholic                | 28
RC Syrian Catholic               | 3

I want to swap the rows and columns like below

Cnanaya Catholic | RC Latin Catholic | RC Syrian Catholic
---------------------------------------------------------
17               | 28                | 3

Please help with any idea.

Upvotes: 1

Views: 1449

Answers (3)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

You can do it using PIVOT like below :

Suppose your current result is by grouping like :

SELECT Denomination, count(id) as count
from table
group by Denomination

Your pivot query will be like :

SELECT * FROM (SELECT Denomination, id from table) d
PIVOT (COUNT(id) FOR Denomination IN ([Cnanaya Catholic],[RC Latin Catholic],[RC Latin Catholic],[RC Syrian Catholic])) p

Upvotes: 1

刘伟科
刘伟科

Reputation: 432

This is an row2column problem. Try this:

select 
  sum(case Denomination when 'Cnanaya Catholic' then Count end) CnanayaCatholic,
  sum(case Denomination when 'RC Latin Catholic' then Count end) RCLatinCatholic,
  sum(case Denomination when 'RC Syrian Catholic' then Count end) RCSyrianCatholic
from YOURTABLE t;

Upvotes: 0

Royi Namir
Royi Namir

Reputation: 148514

you can use sql server PIVOT

Upvotes: 0

Related Questions