All Forums Database
Mike 7 posts Joined 08/05
11 Jan 2006
renaming a column name.....?

Hi Everyone,This is more of a SQL question, but figured I would post here as this group has always been super-helpful : )I want to, in the course of the SQL code, rename a column depending on what is in the column. Specifically, I am doing a count of customers by month, and want the column name to reflect each particular month. I have the month data in my table, but am stumped as to how to get it to be the specific column's name. Anyone have any ideas?As always, thank you all,Mike

Dennis Calkins 12 posts Joined 07/04
11 Jan 2006

Can you give us the Layout of the table currently? And maybe some made up sample data and explain how you want the layout changed?You could try renaming the column on the select.In ANSI sql you can simply change the column name on the select by using NAMED sel MyCol ( NAMED jan );I guess if the Data is number 01 - 12 and you want ASCII names "Jan" - "Dec"you could use a Case statement or a translation table. sel (case (MyCol) when 1 then 'Jan' when 2 then 'Feb' when 3 then 'Mar' when 4 then 'Apr' when 5 then 'May' when 6 then 'Jun' when 7 then 'Jul' when 8 then 'Aug' when 9 then 'Sep' when 10 then 'Oct' when 11 then 'Nov' when 12 then 'Dec' end) As My_Month;

DEEPU 9 posts Joined 10/05
19 Jan 2006

I ASSUME THAT YOU ARE HAVING THE TABLE STRUCTURE LIKE:CUSTOMER...... MNTHX................. 1 Y................. 2 V................. 1Z................. 2 A................. 11 B................. 10 C................. 3SO SQL WILL BE :***************SELCOUNT(CASE WHEN MNTH = '1' THEN CUSTOMER ELSE NULL END) "JAN",COUNT(CASE WHEN MNTH = '2' THEN CUSTOMER ELSE NULL END) "FEB",COUNT(CASE WHEN MNTH = '3' THEN CUSTOMER ELSE NULL END) "MAR",COUNT(CASE WHEN MNTH = '4' THEN CUSTOMER ELSE NULL END) "APR",COUNT(CASE WHEN MNTH = '5' THEN CUSTOMER ELSE NULL END) "MAY",COUNT(CASE WHEN MNTH = '6' THEN CUSTOMER ELSE NULL END) "JUN",COUNT(CASE WHEN MNTH = '7' THEN CUSTOMER ELSE NULL END) "JUL",COUNT(CASE WHEN MNTH = '8' THEN CUSTOMER ELSE NULL END) "AUG",COUNT(CASE WHEN MNTH = '9' THEN CUSTOMER ELSE NULL END) "SEP",COUNT(CASE WHEN MNTH = '10' THEN CUSTOMER ELSE NULL END) "OCT",COUNT(CASE WHEN MNTH = '11' THEN CUSTOMER ELSE NULL END) "NOV",COUNT(CASE WHEN MNTH = '12' THEN CUSTOMER ELSE NULL END) "DEC"FROM XYZHope this will help u. deepu

You must sign in to leave a comment.