Query Help- Unpivot

• yogi123

  SSCrazy

  Points: 2932

  More actions

  December 10, 2013 at 9:08 am

  #287346

  Hello every one

  I need to develop one logic to use in my script

  CREATE TABLE #Temp

  (cStudentID VARCHAR(10),

  CummulativeGPA float,

  Q1GPA Float,

  Q2GPA Float,

  Q3GPA Float,

  Q4GPA Float)

  INSERT INTO #Temp VALUES ('003603938',3.818,NULL,NULL,NULL,NULL)

  I am try to write unPivot Query see below

  select cStudentID,

  Qtr as [Quarter],

  GPA

  from(

  SELECT cStudentID,

  CummulativeGPA ,

  Q1GPA ,

  Q2GPA ,

  Q3GPA ,

  Q4GPA

  FROM #Temp

  ) MyTable

  UNPIVOT

  (GPA FOR Qtr IN ([CummulativeGPA], [Q1GPA], [Q2GPA],[Q3GPA], [Q4GPA]))AS MyUnPivot

  but i am getting the result as below which i does not need

  cStudentIDQuarterGPA

  003603938CummulativeGPA3.818

  The desired output is

  cStudentIDQuarterGPA

  003603938CummulativeGPA3.818

  003603938Q1GPANULL

  003603938Q2GPANULL

  003603938Q3GPANULL

  003603938Q4GPANULL

  Please help me to develop this

  Thanks

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  December 10, 2013 at 9:16 am

  #1672357

  You could use the CROSS APPLY approach[/url]:

  SELECT cStudentID, Quarter, GPA

  FROM #Temp

  CROSS APPLY (VALUES( 'CummulativeGPA', CummulativeGPA),

  ( 'Q1GPA', Q1GPA),

  ( 'Q2GPA', Q2GPA),

  ( 'Q3GPA', Q3GPA),

  ( 'Q4GPA', Q4GPA))x(Quarter, GPA)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• yogi123

  SSCrazy

  Points: 2932

  More actions

  December 10, 2013 at 9:31 am

  #1672361

  Luis Cazares (12/10/2013)

  ---

  You could use the CROSS APPLY approach[/url]:

  SELECT cStudentID, Quarter, GPA

  FROM #Temp

  CROSS APPLY (VALUES( 'CummulativeGPA', CummulativeGPA),

  ( 'Q1GPA', Q1GPA),

  ( 'Q2GPA', Q2GPA),

  ( 'Q3GPA', Q3GPA),

  ( 'Q4GPA', Q4GPA))x(Quarter, GPA)

  Thanks For Your reply.

  I run this Query on SQL Server 2012 and it works but it don't in SQL Server 2005

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  December 10, 2013 at 9:55 am

  #1672372

  This is a possible solution for 2005 because it won't accept the VALUES table construct.

  SELECT cStudentID, Quarter, GPA

  FROM #Temp

  CROSS APPLY (SELECT 'CummulativeGPA', CummulativeGPA UNION ALL

  SELECT 'Q1GPA', Q1GPA UNION ALL

  SELECT 'Q2GPA', Q2GPA UNION ALL

  SELECT 'Q3GPA', Q3GPA UNION ALL

  SELECT 'Q4GPA', Q4GPA)x(Quarter, GPA)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  December 10, 2013 at 10:01 am

  #1672374

  Looking for a solution using UNPIVOT, I found this on BOL:

  null values in the input of UNPIVOT disappear in the output, whereas there may have been original null values in the input before the PIVOT operation.

  So it seems, that you can't go that way, unless someone else comes up with a better option.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• yogi123

  SSCrazy

  Points: 2932

  More actions

  December 10, 2013 at 11:09 am

  #1672398

  Luis Cazares (12/10/2013)

  ---

  This is a possible solution for 2005 because it won't accept the VALUES table construct.

  SELECT cStudentID, Quarter, GPA

  FROM #Temp

  CROSS APPLY (SELECT 'CummulativeGPA', CummulativeGPA UNION ALL

  SELECT 'Q1GPA', Q1GPA UNION ALL

  SELECT 'Q2GPA', Q2GPA UNION ALL

  SELECT 'Q3GPA', Q3GPA UNION ALL

  SELECT 'Q4GPA', Q4GPA)x(Quarter, GPA)

  Nice

  This works for me

  Thanks for Your help