Geek Answers Handbook

Matrix view generated from two selected table columns

Suppose I have a table containing the columns Project_type , Project_No , OS_Platform . Here I restricted Project_type and limited OS_Platform s. I want a database view that creates a matrix between Project_type and OS_Platform .

  MY_TABLE : Project_Type Project_No OS_Platform Drivers 345 Linux WebService 453 Windows Drivers 034 Windows Drivers 953 Solaris DesktopApp 840 Windows WebService 882 Solaris

Now I have Project_type and OS_Platform as selected columns. I need a matrix view of these two columns with different rows and column names.

 Project_Type Linux Windows Solaris WebService null true true Drivers true true true DesktopApp null true null

Can anyone tell me if this is possible. How is this possible?

+4
source share
3 answers

This is basically a PIVOT query in which you PIVOT your rows of data in columns. The easiest way to accomplish this, since you want to use true/null , uses the aggregate function and the CASE statement:

 select project_type, max(case when os_platform ='Linux' then 'true' else null end) Linux, max(case when os_platform ='Windows' then 'true' else null end) Windows, max(case when os_platform ='Solaris' then 'true' else null end) Solaris from yourtable group by project_type

See SQL Fiddle with Demo

Result:

 | PROJECT_TYPE | LINUX | WINDOWS | SOLARIS | --------------------------------------------- | DesktopApp | (null) | true | (null) | | Drivers | true | true | true | | WebService | (null) | true | true |
+2
source

You can also try using the dedicated PIVOT function if it is supported by the SQL product that you are using. For example, the following will work in SQL Server 2005+ :

 SELECT * FROM ( SELECT DISTINCT Project_Type, 'true' AS flag, OS_Platform FROM MY_TABLE ) s PIVOT ( MAX(flag) FOR OS_Platform IN ( Linux, Windows, Solaris ) ) p ;

Oracle Database is another product that supports PIVOT, although I'm not sure which version it was first introduced in. You could execute the above query in Oracle after including each column in the PIVOT IN list in single quotes, for example:

 ... IN ( 'Linux', 'Windows', 'Solaris' ) ...
+1
source

You want to expand / reject your values ​​in order to transfer them to the format of your choice.

Here's a google search for pivot on stack overflow. Any of them will be good for you. https://www.google.com/search?q=sql+pivot+unpivot+site%3Astackoverflow.com&oq=sql+pivot+unpivot+site%3Astackoverflow.com&aqs=chrome.0.57.9985&sugexp=chrome,mod=8&sourceid= chrome & ie = UTF-8

Now there are two types of answers that you will see there. The first is a regular rotation / non-rotation operation. They work very well (easy, not fast) with well-known data sets. That is, if you know all types of projects and platforms, this will work fine.

The second type is a dynamic core or vault created using dynamic SQL. This is messy, but allows you any combination of fields.

Good luck

0
source

More articles:


All Articles