Click here to Skip to main content
15,790,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table with following fields


I have 3 division C01, C02, C03. I wanted to generate a matrix like output like functionality and each division as a separate column like :

Functionality  C01IsInternal  C02IsInternal   C03IsInternal
F1               ON             OFF          OFF
F2               OFF            NULL         ON
F3               ON             ON           NULL

To achieve this I have written following query and it is working fine but I wanted to know if there is a better way to achieve this.

select Distinct( fm.Functionality) as Functionality,
(select IsInternal from FunctionMatrix fm1 where fm1.Division='C01' and fm1.functionality=fm.functionality) as C01IsInternal,
(select IsInternal from FunctionMatrix fm1 where fm1.Division='C02' and fm1.functionality=fm.functionality) as C02IsInternal,
(select IsInternal from FunctionMatrix fm1 where fm1.Division='C03' and fm1.functionality=fm.functionality) as C03IsInternal

from FunctionMatrix fm

The table is small at the moment with just 40 rows but it may grow in future.

Is there a better way to achieve this result ?

1 solution

If your SQL-server is version 2005 or later you may consider using the pivot operator.
Some info on the usage here[^].
Share this answer
virang_21 25-Nov-11 18:43pm    
Yea I have looked at that option but somehow it is not liking Distinct operator with Pivot... or maybe I am missing something there ...
Jörgen Andersson 25-Nov-11 19:06pm    
You can't use the distinct operator when doing a pivot. The grouping is implied as you need to specify a column(s) being aggregated.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900