Pivot and UnPivot Tables

Pivot :

- We can turn Columns into rows. So that single columns Values are converted into Column Names
- When we pivot the table we must use aggregated functions.
                  

CREATE TABLE #tmpNormalTable(CustomerId int,CustomerName varchar(20),NoOfProjects int)

INSERT INTO #tmpNormalTable(CustomerID,CustomerName,NoOfProjects) VALUES (12011,'HealthCare',1)
INSERT INTO #tmpNormalTable(CustomerID,CustomerName,NoOfProjects) VALUES (12000,'Insurance',2)
INSERT INTO #tmpNormalTable(CustomerID,CustomerName,NoOfProjects) VALUES (12011,'HealthCare',21)
INSERT INTO #tmpNormalTable(CustomerID,CustomerName,NoOfProjects) VALUES (12011,'HealthCare',12)
INSERT INTO #tmpNormalTable(CustomerID,CustomerName,NoOfProjects) VALUES (12000,'Insurance',30)
INSERT INTO #tmpNormalTable(CustomerID,CustomerName,NoOfProjects) VALUES (12011,'HealthCare',6)
INSERT INTO #tmpNormalTable(CustomerID,CustomerName,NoOfProjects) VALUES (11100,'Finance',100)



SELECT * FROM #tmpNormalTable

--Get Dymanic Column Names

DECLARE @columns NVARCHAR(2000)
SELECT  @columns = STUFF(
                                                                   (
                                                                             SELECT DISTINCT TOP 100 PERCENT '],[' + t2.CustomerName
                                                                             FROM  #tmpNormalTable AS t2
                                                                             ORDER BY '],[' + t2.CustomerName
                                                                             FOR XML PATH('')
                                                                   )
                                                                   , 1, 2, '') + ']'


DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT CustomerID,'+ @columns + '
FROM
(
          SELECT 
                             t1.CustomerID,
                             t1.CustomerName,
                             t1.NoOfProjects
          FROM #tmpNormalTable AS t1
) p
PIVOT
(
sum([NoOfProjects])
FOR CustomerName IN
( '+
@columns +' )
) AS pvt
ORDER BY CustomerID;'

EXEC(@query)



UNPIVOT :

- We can turn Rows into Columns. So that the Column names are converted into single column values.

CREATE TABLE #tmpNormalUnPivot(CustomerId int,Finance tinyint,Healthcare tinyint,Insurance tinyint)

INSERT INTO #tmpNormalUnPivot(CustomerId,Finance,Healthcare,Insurance) VALUES (11100,100,null,null)
INSERT INTO #tmpNormalUnPivot(CustomerId,Finance,Healthcare,Insurance) VALUES (12000,null,7,null)
INSERT INTO #tmpNormalUnPivot(CustomerId,Finance,Healthcare,Insurance) VALUES (12011,null,null,12)

select * from #tmpNormalUnPivot



SELECT  CustomerID,CustomerName,NoOfProjects
FROM
(
          SELECT CustomerID,Finance,Healthcare,Insurance
          FROM #tmpNormalUnPivot
)p
UNPIVOT
(NoOfProjects FOR CustomerName IN (Finance,Healthcare,Insurance)
) AS Unpvt

04:46 | Category: | 0 comments

Comments (0)

Post a Comment