How to convert rows into column in sql

Question

I have a table,

and need to convert it as

I have tried pivot but can't figure out please help me out .

Answer 1

Pivot does not work with more than one column out-of-the-box There are several approaches to solve this:

Use this table variable for all tests and please state your sample data always copy'n'pasteable. Best was a MCVE (Minimal Complete Verifyable Example) where you set up some code like mine here.

DECLARE @tbl TABLE(ID INT, Code INT,EmployeeName VARCHAR(100),ExamName VARCHAR(100),Board VARCHAR(100),Result VARCHAR(100));
INSERT INTO @tbl VALUES
 (11537,12984,'TheName','SSC','b04','1st')
,(11537,12984,'TheName','HSC','b04','2nd')
,(11537,12984,'TheName','BA(H)','u33','2nd');

This is code to first concatenate your data to one single column. This allows PIVOT:

SELECT p.*
FROM
(
    SELECT tbl.ID
        ,tbl.Code
        ,tbl.EmployeeName
        ,'Exam_' + CAST(ROW_NUMBER() OVER(PARTITION BY tbl.ID ORDER BY tbl.Code) AS VARCHAR(100)) AS ColName
        ,ExamName + ' (' + Board + '): ' + Result AS Concatenated
    FROM @tbl AS tbl
) AS t
PIVOT
(
    MIN(Concatenated) FOR ColName IN(Exam_1,Exam_2,Exam_3 /*add as many as you need*/)
) AS p

The result:

11537   12984   TheName SSC (b04): 1st  HSC (b04): 2nd  BA(H) (u33): 2nd

The next code does quite the same, but creates XML instead of plain text, which allows to separate your data afterwards:

SELECT p.ID
      ,p.Code
      ,p.EmployeeName
      ,E1
      ,E1.value('(/exam/@ExamName)[1]','varchar(100)') AS ExamName1
      ,E1.value('(/exam/@Board)[1]','varchar(100)') AS Board1
      ,E1.value('(/exam/@Result)[1]','varchar(100)') AS Result1
      ,E2
      ,E2.value('(/exam/@ExamName)[1]','varchar(100)') AS ExamName2
      ,E2.value('(/exam/@Board)[1]','varchar(100)') AS Board2
      ,E2.value('(/exam/@Result)[1]','varchar(100)') AS Result2
      ,E3
      ,E3.value('(/exam/@ExamName)[1]','varchar(100)') AS ExamName3
      ,E3.value('(/exam/@Board)[1]','varchar(100)') AS Board3
      ,E3.value('(/exam/@Result)[1]','varchar(100)') AS Result3
FROM
(
    SELECT tbl.ID
        ,tbl.Code
        ,tbl.EmployeeName
        ,'Exam_' + CAST(ROW_NUMBER() OVER(PARTITION BY tbl.ID ORDER BY tbl.Code) AS VARCHAR(100)) AS ColName
        ,(SELECT ExamName AS [@ExamName],Board AS [@Board],Result AS [@Result] FOR XML PATH('exam')) AS AsXML
    FROM @tbl AS tbl
) AS t
PIVOT
(
    MIN(AsXML) FOR ColName IN(Exam_1,Exam_2,Exam_3 /*add as many as you need*/)
) AS p
OUTER APPLY
(
    SELECT CAST(p.Exam_1 AS XML) AS E1
          ,CAST(p.Exam_2 AS XML) AS E2
          ,CAST(p.Exam_3 AS XML) AS E3
) AS CastedToXml

The result:

11537   12984   TheName <exam ExamName="SSC" Board="b04" Result="1st" />    SSC b04 1st <exam ExamName="HSC" Board="b04" Result="2nd" />    HSC b04 2nd <exam ExamName="BA(H)" Board="u33" Result="2nd" />  BA(H)   u33 2nd

This is old-fashioned-pivot which is quite often better then normal pivot:

;WITH Numberd AS
(
    SELECT *
          ,ROW_NUMBER() OVER(PARTITION BY tbl.ID ORDER BY tbl.Code) AS Number
    FROM @tbl AS tbl
)
SELECT ID,Code,EmployeeName
      ,MAX(CASE WHEN Number=1 THEN ExamName END) AS ExamName1
      ,MAX(CASE WHEN Number=1 THEN Board END) AS Board1
      ,MAX(CASE WHEN Number=1 THEN Result END) AS Result1
      ,MAX(CASE WHEN Number=2 THEN ExamName END) AS ExamName2
      ,MAX(CASE WHEN Number=2 THEN Board END) AS Board2
      ,MAX(CASE WHEN Number=2 THEN Result END) AS Result2
      ,MAX(CASE WHEN Number=3 THEN ExamName END) AS ExamName3
      ,MAX(CASE WHEN Number=3 THEN Board END) AS Board3
      ,MAX(CASE WHEN Number=3 THEN Result END) AS Result3
FROM Numberd
GROUP BY ID,Code,EmployeeName

The last option was dynamic SQL...

Answer 2

Thnak you all , But My purpose is solved with this

SELECT 
      he.Id,he.Code,he.Name EmployeeName,en.Name [ExamName],bu.Name        
      [Board],[Result] = CASE WHEN 
             ac.GPA IS NULL THEN ac.Result ELSE CAST(ac.GPA AS VARCHAR) END,
      ac.PassingYear 
INTO #Temp 
FROM H_Employee AS he 
INNER JOIN 
      H_AcademicQualification AS ac ON ac.H_EmployeeId = he.Id 
INNER JOIN 
      ExamName AS en ON en.Id = ac.ExamNameId 
INNER JOIN 
      GroupSubject AS gs ON gs.Id = ac.GroupSubjectId 
INNER JOIN 
      BoardUniversity AS bu ON bu.Id = ac.BoardUniversityId

Then

SELECT
     a.id, a.Code, a.EmployeeName, a.ExamName, a.Board, a.Result, a.Passingyear, 
     b.ExamName, b.Board, b.Result, b.Passingyear, 
     c.ExamName, c.Board, c.Result, c.Passingyear, 
     d.ExamName, d.Board, d.Result, d.Passingyear 
FROM 
    (SELECT 
          Id, Code, EmployeeName ,ExamName = CASE WHEN 
               ExamName LIKE 'S.S.%' THEN 'S.S.C' END,
          Board=Board ,[Result]=CASE WHEN 
               Result IS NULL THEN Result ELSE CAST(Result AS VARCHAR) END,
          Passingyear 
     FROM #temp 
     WHERE ExamName LIKE 'S.S.%') AS a 
LEFT JOIN 
        (SELECT Id, Code, EmployeeName ,ExamName = CASE WHEN 
            ExamName LIKE 'H.S.%' THEN 'H.S.C' END,
         Board=Board,[Result] = CASE WHEN 
            Result IS NULL THEN Result ELSE CAST(Result AS VARCHAR) END,
         Passingyear  
         FROM #temp 
         WHERE ExamName LIKE 'H.S.%') AS b 
     ON a.Id=b.Id 
LEFT JOIN 
        (SELECT Id, Code, EmployeeName ,ExamName = CASE WHEN 
             ExamName LIKE 'B.%' THEN 'Graduate' END, 
         Board=Board, [Result] = CASE WHEN 
             Result IS NULL THEN Result ELSE CAST(Result AS VARCHAR) END,
         Passingyear 
         FROM #temp 
         WHERE ExamName LIKE 'B.%') AS c 
     ON a.Id=c.Id 
LEFT JOIN 
        (SELECT Id, Code, EmployeeName, ExamName = CASE WHEN
             ExamName LIKE 'M.%' THEN 'Post-Graduate' END, 
         Board=Board,[Result] = CASE WHEN 
             Result IS NULL THEN Result ELSE CAST(Result AS VARCHAR) END,
         Passingyear 
         FROM #temp 
         WHERE ExamName LIKE 'M.%') AS d 
     ON a.Id=d.Id

Above query serve me purposes but will try @Shnugo Sample , Any way thank you all .