Generating matrix like result using SQL

Question

0.00/5 (No votes)

See more:

I have a table with following fields

RecordId
Functionality
Division
IsInternal
IsExternal

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 :

HTML

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.

SQL

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 ?

Posted 25-Nov-11 11:29am

virang_21

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Jörgen Andersson · Answer 1 · 2011-11-25T12:36:00

Solution 1

If your SQL-server is version 2005 or later you may consider using the pivot operator.
Some info on the usage here[^].

Posted 25-Nov-11 12:36pm

Jörgen Andersson

Comments

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.