Query Help

• yogi123

  SSCrazy

  Points: 2932

  More actions

  February 27, 2014 at 9:39 am

  #286197

  I need to select dynamic column name in my unpivot query

  the query is as below,

  SELECT *

  FROM

  (

  SELECT Employee_ID, Employee_First_Name, Employee_Last_Name,

  CONVERT(varchar(50), RTRIM(Current_Year_Days_On_Leave )) AS [2012/2013],

  CONVERT(varchar(50), Prior_Year_Days_On_Leave) AS [2011/2012],

  CONVERT(varchar(50), Two_Years_Prior_Days_On_Leave) AS [2010/2011]

  FROM [dbo].[abdc] A

  ) MyTable

  UNPIVOT

  ([Teacher_Leave] FOR SCHOOL_YEAR IN ([2012/2013], [2011/2012], [2010/2011] )) AS MyUnPivot

  in Column name , I hard coded Column name , is it posible any way to dynamic, like

  i set paremter for SchoolYear on top ane select column name accordingly

  like as below,

  DECLARE @sCHOOLyEAR iNT

  SET @sCHOOLyEAR = 2012

  SELECT *

  FROM

  (

  SELECT Employee_ID, Employee_First_Name, Employee_Last_Name,

  CONVERT(varchar(50), RTRIM(Current_Year_Days_On_Leave )) AS [@sCHOOLyEAR/@sCHOOLyEAR+1],

  CONVERT(varchar(50), Prior_Year_Days_On_Leave) AS [@sCHOOLyEAR-1/@sCHOOLyEAR],

  CONVERT(varchar(50), Two_Years_Prior_Days_On_Leave) AS [@sCHOOLyEAR-2/@sCHOOLyEAR-1]

  FROM [dbo].[abdc] A

  ) MyTable

  UNPIVOT

  ([Teacher_Leave] FOR SCHOOL_YEAR IN ([@sCHOOLyEAR/@sCHOOLyEAR+1], [@sCHOOLyEAR-1/@sCHOOLyEAR], [@sCHOOLyEAR-2/@sCHOOLyEAR-1] )) AS MyUnPivot

  please let me know if possible.

  Thanks

• SQLBlimp

  SSCertifiable

  Points: 6340

  More actions

  February 27, 2014 at 10:27 am

  #1692780

  Hi...

  Why not wrap the UNPIVOT query in a subquery and select where after you unpivot?

  Just my $0.02

  John.