Hi priyajsr,
Refer the below sample query.
DECLARE @Test AS TABLE([Account Head] VARCHAR(20), [Collection Amount] INT)
INSERT INTO @Test VALUES('Admission Dept',25000)
INSERT INTO @Test VALUES('Electric Dept',60000)
SELECT MAX(CASE WHEN [Account Head] = 'Admission Dept' THEN [Collection Amount] END) AS [Admission Dept]
,MAX(CASE WHEN [Account Head] = 'Electric Dept' THEN [Collection Amount] END) AS [Electric Dept]
--FROM (SELECT s.*,ROW_NUMBER() OVER (PARTITION BY [Collection Amount] ORDER BY (SELECT 0)) AS Test FROM @Test s) a
FROM @Test
--OR
--;WITH CTE AS
--(
-- SELECT [Account Head],[Collection Amount],ROW_NUMBER() OVER(PARTITION BY [Account Head] ORDER BY (SELECT 0)) AS RowNo
-- FROM @Test
--)
SELECT MAX(CASE WHEN [Account Head] = 'Admission Dept' THEN [Collection Amount] END) AS [Admission Dept]
,MAX(CASE WHEN [Account Head] = 'Electric Dept' THEN [Collection Amount] END) AS [Electric Dept]
FROM @Test
Output
Admission Dept Electric Dept
25000 60000
Admission Dept Electric Dept
25000 60000
