How to alias column of table from another table

Question

I have table say tblModel.

it has few fields like

ID,Section,LineItem, F1,F2,F60

i have another tables say tblPeriods
it has only periods like 2010 FY,2011 FY,2012 FY, 1Q 2013,2Q 2013,3Q 2013,4Q 2013,2013 FY,
1Q 2014,2Q 2014,3Q 2014,4Q 2014,2014 FY, to

1Q 2060,2Q 2060,3Q 2060,4Q 2060,2060 FY,

i want to alias F1 with 2010 FY
F2 with 2011 FY
F3 with 2012 FY
F4 with 1Q 2013
F5 with 2Q 2013
F6 with 3Q 2013
F7 with 4Q 2013
F8 with 2013 FY

is it possible because there is no relation between two tables tblModel & tblPeriods

please suggest a good solution. Thanks

Answer 1

Hi @Sudip Bhatt ,

As mentioned by Guoxiong, you could create a relational table and create a dynamic statement to produce this select statement with expected alias.

You could refer below simple example:

--create tblModel table  
create table tblModel  
(  
ID int,  
Section int,  
LineItem varchar(10),  
F1 int,  
F2 int,  
F3 int,  
F4 int  
)  
  
--insert one row of sample data into tblModel table  
insert into tblModel values  
(1,1,'Item1',10,20,30,40)  
  
--create tblPeriods table   
create table tblPeriods  
(  
[2010 FY] int,  
[2011 FY] int,  
[2012 FY] int,  
[1Q 2013] int  
)  
  
--create relational  table named tblPeriods  
create table tblModelPeriods  
(  
RawName VARCHAR(20),   
RealName VARCHAR(20))  
  
insert into tblModelPeriods values  
('F1','2010 FY'),  
('F2','2011 FY'),  
('F3','2012 FY'),  
('F4','1Q 2013')  
  
--produre this dynamic statement  
DECLARE @SQL NVARCHAR(MAX)  
  
SELECT @SQL = STUFF(( SELECT ', ' + QUOTENAME(C.COLUMN_NAME) + ' AS ' + QUOTENAME(F.RealName)  
FROM INFORMATION_SCHEMA.COLUMNS C   
INNER JOIN tblModelPeriods F ON C.COLUMN_NAME = F.RawName  
WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME = 'tblModel'  
ORDER BY C.ORDINAL_POSITION FOR XML PATH('')),1,2,'')  
  
SELECT @SQL = 'SELECT ID,Section,LineItem,' + @SQL + ' FROM tblModel'  
  
EXECUTE(@SQL)  

Output:

ID	Section	LineItem	2010 FY	2011 FY	2012 FY	1Q 2013  
1	1	Item1	10	20	30	40  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can create a relational table, i.e., tblModel_tblPeriods between tblModel and tblPeriods. One column is from tblModel and the other is from tblPeriods based on your description.