Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
I have data as

Year_1 Year_2 Year_3 Year_1_Value Year_2_Value Year_3_Value
---------------------------------------------------------------------
2010 2011 2012 155 119 145


how can i select following

select Year_1_Value AS Year_1 from table;
(Similar to select Year_1_Value AS '2010' from table)

output like:

2010
------
155

can any one help me??
thanx in advance!!<b></b>
Posted
Comments
Yvan Rodrigues 2-Sep-11 12:46pm    
Is there any reason these can't be put into to tables?

This looks like a bad design. Instead of having years and values as separate columns, you should use a table with two columns:
- Year
- Value

So the data in the table would be:
Year Value
---- -----
2010 155
2011 119
2012 145

With that, you would query for proper year always from the same column. What comes to the column name, if you want to show the year in the UI, you should fetch the proper label/text/etc from the data (from Year column).
 
Share this answer
 
Hi mika,

thnx for suggestion, but i have to do it in that way bcoz of such criteria, and i got the solution through the following code using Pivoting....

CREATE PROCEDURE `table_name`(
PYear_1 VARCHAR(50),
PYear_2 VARCHAR(50),
PYear_3 VARCHAR(50),
PYear_4 VARCHAR(50),
PYear_5 VARCHAR(50),
PYear_6 VARCHAR(50),
PYear_7 VARCHAR(50),
PYear_8 VARCHAR(50)
)
BEGIN

SET @NYear_1 =PYear_1;
SET @NYear_2 =PYear_2;
SET @NYear_3 =PYear_3;
SET @NYear_4 =PYear_4;
SET @NYear_5 =PYear_5;
SET @NYear_6 =PYear_6;
SET @NYear_7 =PYear_7;
SET @NYear_8 =PYear_8;


SET @s = CONCAT("SELECT id,
SUM( IF( Year_1 = ", @NYear_1,", Year_1_Value, 0 ) ) AS '", @NYear_1,"',
SUM( IF( Year_2 = ", @NYear_2,", Year_2_Value, 0 ) ) AS '", @NYear_2,"',
SUM( IF( Year_3 = ", @NYear_3,", Year_3_Value, 0 ) ) AS '", @NYear_3,"',
SUM( IF( Year_4 = ", @NYear_4,", Year_4_Value, 0 ) ) AS '", @NYear_4,"',
SUM( IF( Year_5 = ", @NYear_5,", Year_5_Value, 0 ) ) AS '", @NYear_5,"',
SUM( IF( Year_6 = ", @NYear_6,", Year_6_Value, 0 ) ) AS '", @NYear_6,"',
SUM( IF( Year_7 = ", @NYear_7,", Year_7_Value, 0 ) ) AS '", @NYear_7,"',
SUM( IF( Year_8 = ", @NYear_8,", Year_8_Value, 0 ) ) AS '", @NYear_8,"'
FROM table where tableid=19 GROUP BY id");


PREPARE stmt FROM @s;

EXECUTE stmt;



END$$
 
Share this answer
 

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