Pivot / UnPivot Example

SQL Query :

   DECLARE @Tbl TABLE(Col1 INT, A VARCHAR(1), B VARCHAR(1), C VARCHAR(1) )

   INSERT INTO @Tbl
    SELECT 1, 'a', 'b', 'c'
    UNION 
    SELECT 2, 'd', 'e', 'f'
    UNION 
    SELECT 3, 'g', 'h', 'i'

   SELECT 
    *
   FROM
   (
    SELECT *
    FROM
    (
     SELECT 
      Col1 AS Cols
      , A AS [A(N1)] 
      , B AS [B(N2)] 
      , C AS [C(N3)] 
     FROM @Tbl
    ) A
    UNPIVOT
    ( [Value] For [Col1] IN ( 
           [A(N1)]
           , [B(N2)]
           , [C(N3)]
          ) ) AS upvt
   ) B
   PIVOT
   (
    MIN ( [Value])
    FOR [Cols]
    IN ([1], [2], [3])
   ) AS pvt;

Input :

    ----------------
    Col1       A B C
    ----------------
    1          a b c 
    2          d e f
    3          g h i
    ----------------

OutPut :

    
    -------------------
    Col1          1 2 3
    -------------------
    A(N1)         a d g
    B(N2)         b e h
    C(N3)         c f i
    -------------------

PIVOT

DECLARE @FromDate DATETIME
 DECLARE @ToDate   DATETIME
 
 ----------------
 SET NOCOUNT ON

 SET @FromDate = '10/1/2009'
 SET @ToDate   = '10/1/2010'
 
 CREATE Table #tblSchool ( SchoolName VARCHAR(20), [Date] DATETIME, NoOfBoys INT, NoOfGirls INT )  
 
 DECLARE @SqlQuery VARCHAR(MAX)
 DECLARE @Months   VARCHAR(MAX)
 DECLARE @DisplayMonths   VARCHAR(MAX)
 SET @SqlQuery = ''
   
 INSERT INTO #tblSchool VALUES ('aa','1/1/2010', 10, 15)  
 INSERT INTO #tblSchool VALUES ('aa','1/2/2010', 15, 17)  
 INSERT INTO #tblSchool VALUES ('aa','2/1/2010', 18, 19)  
 INSERT INTO #tblSchool VALUES ('aa','2/2/2010', 21, 22)  
 
 INSERT INTO #tblSchool VALUES ('bb','1/1/2010', 11, 17)  
 INSERT INTO #tblSchool VALUES ('bb','1/2/2010', 16, 19)  
 INSERT INTO #tblSchool VALUES ('bb','2/1/2010', 19, 21)  
 INSERT INTO #tblSchool VALUES ('bb','2/2/2010', 25, 25)  
 
 ;with MonthData AS 
 (
  SELECT DATEADD(DD, 1 - DAY( @FromDate ), @FromDate ) AS MonthDates
  
  UNION ALL
  
  SELECT DATEADD(MM, 1, MonthDates ) AS MonthDates
  FROM MonthData
  WHERE MonthDates <= @ToDate
 )
 
 SELECT * 
 INTO #MonthData
 FROM MonthData
 ORDER BY MonthDates
 
 SELECT @Months = STUFF(
       (
        SELECT ', [' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 1, 3 )  
           + ' ' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 8, 4 )
           + ']'
        FROM #MonthData
        FOR XML PATH('')
       ),1,1,'')

 SELECT @DisplayMonths = STUFF(
       (
        SELECT ', ISNULL( [' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 1, 3 )  
           + ' ' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 8, 4 )
           + '], 0 ) AS [' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 1, 3 )  
           + ' ' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 8, 4 )
           + ']'
        FROM #MonthData
        FOR XML PATH('')
       ),1,1,'')
        
 SET @SqlQuery ='SELECT SchoolName, ' + @DisplayMonths + '
     FROM  
     (  
      SELECT   
       SchoolName  
       , (  
        SUBSTRING( CONVERT( VARCHAR(20), [Date] ), 1, 3 )  
        + '' '' + SUBSTRING( CONVERT( VARCHAR(20), [Date] ), 8, 4 )  
         ) AS BillMonth  
       , ( NoOfBoys + NoOfGirls ) AS TotalStudent
      FROM #tblSchool  
      WHERE #tblSchool.[Date] BETWEEN ''' + CAST( @FromDate AS VARCHAR(20) ) + ''' AND ''' + CAST( @ToDate AS VARCHAR(20) ) + '''
     ) AS ItemDetail  
     PIVOT ( SUM(TotalStudent) FOR BillMonth IN ( ' + @Months + ' ) ) AS pvt '

 PRINT ( @SqlQuery )
 EXECUTE ( @SqlQuery )

 DROP TABLE #tblSchool
 DROP TABLE #MonthData

PIVOT IN Multipal Column

DECLARE @Table TABLE ( OrderId INT, ItemId INT, ItemQty INT, ItemValue INT)

INSERT INTO @Table VALUES (1,1,1,10)
INSERT INTO @Table VALUES (1,2,2,20)
INSERT INTO @Table VALUES (1,3,3,30)

INSERT INTO @Table VALUES (2,1,1,11)
INSERT INTO @Table VALUES (2,2,2,21)
INSERT INTO @Table VALUES (2,3,3,31)

INSERT INTO @Table VALUES (3,1,1,12)
INSERT INTO @Table VALUES (3,2,2,22)
INSERT INTO @Table VALUES (3,3,3,32)

SELECT
    OrderId
    , SUM([Q1]) AS Q1
    , SUM([Q2]) AS Q2
    , SUM([Q3]) AS Q3
    , SUM([V1]) AS V1
    , SUM([V2]) AS V2
    , SUM([V3]) AS V3
FROM
(
    SELECT
        OrderId
        , 'Q' + CAST( ItemId AS VARCHAR(10) ) AS QtyItemCd
        , ItemQty
        , 'V' + CAST( ItemId AS VARCHAR(10) ) AS ValueItemCd
        , ItemValue
    FROM @Table
) AS OrderDetail
PIVOT ( SUM(ItemQty)   FOR QtyItemCd   IN ( [Q1], [Q2], [Q3] ) ) AS pvtQty
PIVOT ( SUM(ItemValue) FOR ValueItemCd IN ( [V1], [V2], [V3] ) ) AS pvtValue
GROUP BY OrderId
ORDER BY OrderId