CODEWITHSUNDEEP
mysqlviewsubquery
OK_Sooner
OK_Sooner

Reputation: 131

Create views by subquerying same table

I have a table that has the following columns:

Table 1:

YR     Name     Code 
---------------------
2011     A       1a      
2012     A       2a 
2013     A       3a

I want to create a view that looks like this:

View 1:

Name     2013Code     2012Code     2011Code
---------------------------------------------------------------
A           1a           2a           3a

Do I use a subqueries to fill in each column?

Upvotes: 1

Views: 676

Answers (1)

AdamMc331
AdamMc331

Reputation: 16691

If you only have these three (or a few finite year values) you can use case statements to pivot this table easily:

CREATE VIEW view1 AS
SELECT 
  name, 
  MAX(CASE WHEN yr = 2013 THEN code END) AS '2013Code',
  MAX(CASE WHEN yr = 2012 THEN code END) AS '2012Code',
  MAX(CASE WHEN yr = 2011 THEN code END) AS '2011Code'
FROM myTable
GROUP BY name;

However, if you need something dynamic, I'd reference this article for information on dynamic pivot tables. The query would look like this:

SET @query = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN yr = ''', yr, ''' THEN code END) AS ', yr, 'code')) INTO @query FROM myTable;
SET @query = CONCAT('SELECT name, ', @query, ' FROM myTable GROUP BY name');

The first part of this will set up the template for select the year values as column names, while the second will select those values from the table and group by name. The only thing left to do is execute this prepared statement:

PREPARE stmt FROM @query;
EXECUTE stmt;

Here is an SQL Fiddle, both with the hard coded and dynamic versions so you can see that they produce the same results.

Upvotes: 1

Related Questions