Sql Server Pivot query

Question

I have below data in sql server:

Testnum Machine Name    Certification
1234    Computer 1  CCNA
2345    Computer 2  CNSE
2345    Computer 2  BBC
1234    Computer 2  CNBC
2345    Computer 3  J2EE

I want it to look like below:

Testnum Machine Name    Certification   Certification   Certification   Certification
1234    Computer 1  CCNA    CNBC        
2345    Computer 2  CNSE    BBC J2EE    

Can someone please help me, I tried various pivots but as I cannot use any aggregate function in this dataset is having tough time setting it up.

Thanks

Answer 1

You can get your desired results by using ROW_NUMBER() in addition to PIVOT:

;WITH cte AS (SELECT *,'Certification'+CAST(ROW_NUMBER() OVER(PARTITION BY Testnum ORDER BY MachineName) AS VARCHAR(5))RN
              FROM Table1)
SELECT Testnum
      ,MIN(Machinename)MachineName
      ,MAX(Certification1)Certification1
      ,MAX(Certification2)Certification2
      ,MAX(Certification3)Certification3
FROM cte
PIVOT (MAX(Certification) FOR RN IN (Certification1,Certification2,Certification3))p
GROUP BY TestNum

Demo: SQL Fiddle

A dynamic version will go something like this, but can't test at the moment:

DECLARE @cols AS VARCHAR(MAX)
       ,@sql  AS VARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + 'Certification'+CAST(ROW_NUMBER() OVER(PARTITION BY Testnum ORDER BY MachineName) AS VARCHAR(5))RN 
                   FROM Table1 c
                   FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') 
                   ,1,1,'') 

SET @sql = 'WITH cte AS (SELECT TestNum,Certification,MachineName) AS VARCHAR(5))RN
                         FROM Table1
                          )
            SELECT *
            FROM cte
            PIVOT (MAX(Certification) FOR RN IN ('+@cols+'))p
            '
EXEC (@sql)

Answer 2

Hi we can do using max condition also

DECLARE @Table1  TABLE 
    ([Testnum] int, [MachineName] varchar(10), [Certification] varchar(4))
;

INSERT INTO @Table1
    ([Testnum], [MachineName], [Certification])
VALUES
    (1234, 'Computer 1', 'CCNA'),
    (2345, 'Computer 2', 'CNSE'),
    (2345, 'Computer 2', 'BBC'),
    (1234, 'Computer 2', 'CNBC'),
    (2345, 'Computer 3', 'J2EE')
;

select Testnum,MachineName,MAX(case WHEN MachineName = 'Computer 1' THEN d.Certification END)As Certification1,
MAX(case WHEN MachineName = 'Computer 2' THEN Certification END)As Certification2,
MAX(case WHEN MachineName = 'Computer 3' THEN Certification END)As Certification3 from
(Select t.Testnum,t.MachineName,Certification,ROW_NUMBER()OVER(partition by t.MachineName order by t.Testnum )as seq
from @Table1 t
 ) d
GROUP BY Testnum,MachineName,seq