--Create table

CREATE TABLE IndAusSeries
(
TeamA VARCHAR(3)
,TeamB VARCHAR(3)
,MatchDate DATETIME
,WinsBy VARCHAR(3)
)
GO

--Insert Data

INSERT INTO IndAusSeries(TeamA,TeamB,MatchDate,WinsBy)
VALUES
('Ind','Aus','01-10-2014','Ind'),
('Ind','Aus','01-15-2014','Ind'),
('Ind','Aus','01-19-2014','Ind'),
('Ind','Aus','01-23-2014','Aus'),
('Ind','Aus','01-27-2014','Ind'),
('Ind','Aus','01-31-2014','Ind')

--Verify Data

SELECT TeamA,TeamB,MatchDate,WinsBy FROM IndAusSeries

--

-----------Method 1 | Pawan Kumar Khowal ----------------


;WITH CTE1 AS
(
    SELECT * , ROW_NUMBER() OVER  (ORDER BY %%Physloc%%) rnk 
	FROM IndAusSeries
)
,CTE2 AS
(
	SELECT *, 
		CASE WHEN WinsBy = lag(WinsBy) 
		over(order by rnk) THEN 0 ELSE 1 END cols 
	FROM CTE1 c2		 
)
,CTE3 AS
(
	SELECT * , SUM(cols) OVER (ORDER BY rnk) grouper FROM CTE2
)
,CTE4 AS
(
	SELECT grouper,COUNT(grouper) Maxy FROM CTE3 GROUP BY grouper
)
,CTE5 AS
(
	SELECT MAX(Maxy) Maxy FROM CTE4
)
SELECT c.TeamA , c.TeamB , c.MatchDate , c.WinsBy 
FROM CTE4 a 
INNER JOIN CTE5 b ON a.Maxy = b.Maxy 
INNER JOIN CTE3 c ON c.grouper = a.grouper

--

--

-----------Method 2 | Deepak Sharma ----------------


WITH CTE 
AS 
(
	SELECT * 
		, RANK() OVER (PARTITION BY a.WinsBy,a.lag ORDER BY a.MatchDate) rnk
		, ROW_NUMBER() OVER (ORDER BY MatchDate) rnum 
	FROM 
	(
		SELECT *, 
			REPLACE(lag(winsby,1,0) OVER (ORDER BY MatchDate),'0','Ind') lag 
		FROM IndAusSeries
	) a
)
SELECT TeamA,TeamB,MatchDate,WinsBy from CTE where rnk = rnum


--

--

-----------Method 3 | Isha Mattoo ----------------


;WITH CTE AS 
(
	SELECT TeamA,TeamB,MatchDate,WinsBy
        
,ISNULL(Lead(WinsBy) OVER (ORDER BY (SELECT NULL)),0) AS NextWin
	FROM IndAusSeries
)
,CTE2 AS 
(
	SELECT *,IIF(NextWin<> WinsBy,1,0) AS a
        ,ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) AS b
	from cte
)
,CTE3 AS 
(
	SELECT *, b - ISNULL(lag(b) OVER (ORDER BY (SELECT NULL)),0) AS C FROM CTE2
	WHERE a = 1
)
SELECT t2.* FROM cte3 t1
RIGHT OUTER JOIN IndAusSeries t2 ON t1.MatchDate = t2.MatchDate
WHERE t2.MatchDate  <= (SELECT MatchDate FROM cte3 WHERE C = (SELECT MAX(C) FROM CTE3))
AND t2.WinsBy = (SELECT WinsBy FROM cte3 WHERE C = (SELECT MAX(C) FROM CTE3))


--

--Create table

CREATE TABLE TestTranspose
(
 Year1 INT
,Month1 VARCHAR(3)
,Amount INT
)
GO

--Insert Data

INSERT INTO TestTranspose Values (2014,'Jan',1000),(2014,'Feb',1200),(2014,'Mar',1300)
INSERT INTO TestTranspose Values (2013,'Jan',3000)

--Verify Data

SELECT Year1,Month1,Amount FROM TestTranspose

--


--Solution 1



;WITH CTE AS 
(
       SELECT 1 rnk , 'Jan' Mm
       UNION ALL
       SELECT 2 , 'Feb'
       UNION ALL
       SELECT 3 , 'Mar'
)
,CTE3 AS
(
       SELECT T.* , rnk 
       FROM TestTranspose T INNER JOIN CTE c ON C.Mm = T.Month1
)
,CTE4 AS
(
       SELECT Year1,[1] Jan ,[2] Feb ,[3] Mar FROM CTE3
       PIVOT 
       (SUM(Amount) FOR rnk IN ([1],[2],[3])) p
) 
SELECT Year1 , SUM(Jan) Jan , SUM(Feb) Feb , SUM(Mar) Mar FROM CTE4 GROUP  BY Year1


--