Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

how create a view that contain the employee prior experience year fields as the column name in SQL Server

on Wed Dec 13, 2017 11:40 am
my table-1 is like:
Code:
id    name
1     ann
2     Kim
3     joe
4     Reb
5     anu
table-2 is like:
Code:
id  start_date  employer
1   8/7/2011    Wipro
1   9/6/2012    Sutherland
2   1/7/2010    Infosys
2   7/9/2011    Wipro
3   8/2/2011    Infosys
3   9/8/2012    cape_gemini
3   8/6/2013    Wipro
I want to create a 'view' fro these two tables the output is like:
Code:
id   name    2010       2011          2012         2013
1    and     null      Wipro         Sutherland     null
2    Kim    Infosys    Wipro         null           null
3    joe    null       Infosys       cape_gemini   Wipro
Answers
use pivot. like this
Code:
with cte
as
(
SELECT
  T1.ID,
  T1.NAME,
  T2.Employer,
  start_date = YEAR(T2.start_date)
  FROM T1
    INNER JOIN T2
      ON T1.ID = T2.ID
 )
 select
 *
 from cte
 pivot
 (
     max(employer)
   for
   start_date in
   (
     [2010],[2011],[2012],[2013]
     )
)pvt

Result
Back to top
Permissions in this forum:
You can reply to topics in this forum