SQL:PIVOT AND UN-PIVOT - SQL

PIVOT AND UN-PIVOT - SQL

In several scenarios we would have to change the rows to columns and vice versa in a our select query to overcome some bad database designs.Unpivot and pivot does exactly that. All though SSIS provides you with the feature to do unpivot and pivot , it is highly advisable to do it in sql query , unless you have the data coming  from a non – oledb source or from two different sources.

UNPIVOT

Unpivot rotates the columns of the table to the column values.

Following shows a simple example

Term	ENGLISH	MATHS	SCIENCE	FRENCH
MidTerm1	78	86	76	78
MidTerm2	77	44	76	56
MidTerm3	98	47	87	76
MidTerm4	35	89	77	69

SELECT   Term ,

        Subject,

         Marks

FROM     (SELECT Term,ENGLISH,MATHS,SCIENCE,FRENCH

          FROM Scores WHERE STUDENT_ID = 345950) p

         UNPIVOT

         (Marks

          FOR Subject IN ([ENGLISH],[MATHS],[SCIENCE],[FRENCH] ) ) AS unpvt

ORDERBY Term,

        Subject;

GO

TERM	SUBJECT	MARKS
MidTerm1	ENGLISH	78
MidTerm1	MATHS	86
MidTerm1	SCIENCE	76
MidTerm1	FRENCH	78
MidTerm2	ENGLISH	77
MidTerm2	MATHS	44
MidTerm2	SCIENCE	76
MidTerm2	FRENCH	56
MidTerm3	ENGLISH	98
MidTerm3	MATHS	47
MidTerm3	SCIENCE	87
MidTerm3	FRENCH	76
MidTerm4	ENGLISH	35
MidTerm4	MATHS	89
MidTerm4	SCIENCE	77
MidTerm4	FRENCH	69

Unpivot on Multiple Columns

Consider the following scenario

Company	CEOFName	CEOLName	ManagerFname	ManagerLname	ExecutiveFname	ExecutiveLname
Alpha	John	Smith	Michael	Scott	Tim	Hardy
Beta	Pamela	Lee	Thomas	Right	Jim	Watson

Now if you require the result set such as

Company	ContactFirstName	ContactLastName
Alpha	John	Smith
Alpha	Michael	Scott
Alpha	Tim	Hardy
Beta	Pamela	Lee
Beta	Thomas	Right
Beta	Jim	Watson

For the desired result you will have to unpivot multiple columns , but that is not the main challenge .It is quite tricky to map the firstname with the respective last name.

Select Company,

                FirstName,

                LastName

From

(select

Company, CEOFName, CEOLName, ManagerFname, ManagerLname, ExecutiveFname, ExecutiveLname

From Company) main

Unpivot (FirstName for FirstNames in (CEOFName, ManagerFname, ExecutiveFname) fn

Unpivot (LastName for LastNames in  (CEOLName, ManagerLname, ExecutiveFname) ln

WhereRight(FirstName,1)  = Right(LastName,1)  

The where clause “Where Right(FirstName,1)  = Right(LastName,1) “ is used for mapping the firstName against the respective lastname.

That is using the number on the end of the column names to ties together the supplier with the city, so  firstName1  = lastname1, firstName2  = lastname2

Alternatively you could also use a substring or the following

  WHERE ( (FirstNames = ‘CEOFName’

                AND LastNames = ' CEOLName ')

            OR  (FirstNames = ' ManagerFname '

                AND LastNames = ' ManagerLname ')

OR  (FirstNames = ' ExecutiveFname '

                AND LastNames = ' ExecutiveFname')) 

PIVOT

Pivot lets you change the unique values in one column in to columns in the result set.

Lets consider the below example again. A sample example is provided  for pivoting the below dataset.

TERM	SUBJECT	MARKS
MidTerm1	ENGLISH	78
MidTerm1	MATHS	86
MidTerm1	SCIENCE	76
MidTerm1	FRENCH	78
MidTerm2	ENGLISH	77
MidTerm2	MATHS	44
MidTerm2	SCIENCE	76
MidTerm2	FRENCH	56
MidTerm3	ENGLISH	98
MidTerm3	MATHS	47
MidTerm3	SCIENCE	87
MidTerm3	FRENCH	76
MidTerm4	ENGLISH	35
MidTerm4	MATHS	89
MidTerm4	SCIENCE	77
MidTerm4	FRENCH	69

SELECT   Term,[ENGLISH],[ MATHS],[ SCIENCE],[ FRENCH]  

FROM     (SELECT Term,Subject,MarksFROM Table) AS pvt

         PIVOT

         (SUM(Marks) – Aggregate is to get unique values for cells

          FOR SUBJECT IN ( [ENGLISH],[ MATHS],[ SCIENCE],[ FRENCH] ) ) AS pvo

ORDERBY TERM;