Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help- Unpivot Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 9:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:24 AM
Points: 113, Visits: 329
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

cStudentID	Quarter		GPA
003603938 CummulativeGPA 3.818


The desired output is

cStudentID	Quarter		GPA
003603938 CummulativeGPA 3.818
003603938 Q1GPA NULL
003603938 Q2GPA NULL
003603938 Q3GPA NULL
003603938 Q4GPA NULL


Please help me to develop this

Thanks
Post #1521568
Posted Tuesday, December 10, 2013 9:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 2,786, Visits: 5,969
You could use the CROSS APPLY approach:

SELECT cStudentID, Quarter, GPA
FROM #Temp
CROSS APPLY (VALUES( 'CummulativeGPA', CummulativeGPA),
( 'Q1GPA', Q1GPA),
( 'Q2GPA', Q2GPA),
( 'Q3GPA', Q3GPA),
( 'Q4GPA', Q4GPA))x(Quarter, GPA)




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521570
Posted Tuesday, December 10, 2013 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:24 AM
Points: 113, Visits: 329
Luis Cazares (12/10/2013)
You could use the CROSS APPLY approach:

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
Post #1521574
Posted Tuesday, December 10, 2013 9:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 2,786, Visits: 5,969
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521586
Posted Tuesday, December 10, 2013 10:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 2,786, Visits: 5,969
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521588
Posted Tuesday, December 10, 2013 11:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:24 AM
Points: 113, Visits: 329
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
Post #1521615
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse