SQL Query to PIVOT? data

Transact-SQL
1

I have an SQL query that contains multiple rows of data with field names like Computer, OS, patchBulletinID and CVE. I need to take the CVE values and CVSS Score and combine them into the one row that matches the duplicate BulletinID field. So in stead of having

patchBulletinID CVE CVSS_Score
1234 MS100 3.3
1234 MS101 3
1234 MS102 7
3456 MS103 8
3456 MS104 9

I need it to be...
patchBulletinID CVE_WithCVSS_Score
1234 MS100, 3.3, MS101, 3, MS102, 7
3456 MS103, 8, MS104, 9

Here is my original query if needed... Thanks in advance for any help!

SELECT
sm.[smachName] AS Machine,
scan.[ScanDate],
scan.[ScanName],
CASE
WHEN detectedPatch.[itemType] = 3 THEN 'YES'
WHEN detectedPatch.[itemType] = 4 THEN 'NO'
WHEN detectedPatch.itemType = 0 THEN 'YES'
END AS Installed,
product.[prodName] AS Patch_Name,
patch.[patchQNumbers] AS QNumber,
Cve.cveId AS CVE,
Cve.cvssV2 AS CVSS_Score,
patch.[patchBulletinID] AS BulletinID,
patch.[patchBulletinURL] AS BulletinURL,
patch.[patchLevel] AS Patch_Level,
patchAppliesTo.[pspplMSSeverity] AS Severity,
sm.[smachOSID]
FROM
[dbo].[ManagedMachines] AS machine
INNER JOIN [dbo].[ScanMachines] AS sm ON
machine.[mmKey] = sm.[smachmmKey]
INNER JOIN [dbo].[Scans] AS scan ON
scan.[ScanID] = sm.[smachScanID]
INNER JOIN [dbo].[ScanItems] AS detectedPatch ON
sm.[smachID] = detectedPatch.[itemMachineID]
INNER JOIN [dbo].[LinkPatchProduct] AS patchAppliesTo ON
patchAppliesTo.[pspplID] = detectedPatch.[itempspplID]
INNER JOIN [dbo].[Patches] AS patch ON
patch.[patchID] = patchAppliesTo.[pspplpatchID]
LEFT JOIN PatchCve pc ON
pc.patchUid = PatchAppliesTo.patchUid
LEFT JOIN cve ON
cve.Id = pc.cveId
INNER JOIN [dbo].[LinkSPProduct] AS productLevel ON
productLevel.[spplID] = patchAppliesTo.[pspplspplID]
INNER JOIN [dbo].[Products] AS product ON
product.[prodID] = productLevel.[spplprodID]
INNER JOIN [dbo].[ServicePacks] AS sp ON
sp.[spID] = productLevel.[spplspID]
LEFT OUTER JOIN [dbo].[VirtualMachine] AS virtualMachine ON
virtualMachine.[id] = sm.[virtualMachineId]
LEFT OUTER JOIN [dbo].[VirtualServer] AS virtualServer ON
virtualMachine.[virtualServerId] = virtualServer.[id]
WHERE
--detectedPatch.[itemType] <> 2 AND --For CUs
detectedPatch.[itemType] = 4 AND
patch.[patchBulletinID] <> N'' AND
patch.[patchQNumbers] <> N''
AND (scanID = 7411)
--AND smachName = 'ap01img0'
--AND detectedPatch.itemType=4
ORDER BY
-- Report is grouped by Scan, Machine and Product.
scan.[ScanDate] DESC,
scan.[ScanID] DESC,
sm.[smachName] ASC,
sm.[smachDomainName] ASC,
virtualMachine.[path] ASC,
virtualServer.[name] ASC,
product.[prodName] ASC,
patch.[patchLevel] DESC,
detectedPatch.[itemType] DESC,
patch.[patchBulletinID] ASC,
patch.[patchQNumbers] ASC

2

hi

hope this helps

using STRING_AGG function

create data script

DROP TABLE IF EXISTS #DATA
CREATE TABLE #DATA ( patchBulletinID INT , CVE VARCHAR(20), CVSS_Score FLOAT)
INSERT INTO #DATA SELECT 1234,'MS100',3.3
INSERT INTO #DATA SELECT 1234,'MS101',3
INSERT INTO #DATA SELECT 1234,'MS102',7
INSERT INTO #DATA SELECT 3456,'MS103',8
INSERT INTO #DATA SELECT 3456,'MS104',9
SELECT * FROM #DATA

SELECT 
     patchBulletinID 
	 , STRING_AGG(CVE+','+CAST(CVSS_Score AS VARCHAR),',')  
FROM 
   #DATA 
GROUP BY 
      patchBulletinID

image