Convert Rows to Columns based on contents? (PIVOT?)

Question

I am trying to create a report using Microsoft SQL Server Management Studio 2012. I've got a fair amount of experience writing SQL reports, but have never had to use what I think should be a PIVOT until now.

Our RMM - Kaseya, uses the same column for all "custom audit fields", we have made a few custom "columns" like: SMART Drive Model, SMART Status, and SMART Last Updated date. You'd think these are defined as their own columns, but no -- regular entries in a cell, linked to an agentguid.

From looking at the table below, the fieldValue on the right is dependent on the fieldName to the left of it. I'm not sure how to turn these rows into their own columns based on the fieldName.

I'm seeking a query that produces five columns:

agentguid
Model
Size
Updated
Status

I will later run an INNER JOIN on this data, to compare against the agentguid and be able to customize my final report.

Below is a sample of data

SELECT * FROM vSystemInfoManual;

Machine_GroupID	agentGuid	fieldName	fieldValue
COMPUTER1.companyB	101159575290188	SMART Drive Model	PLEXTOR PX-256M5Pro ATA Device
COMPUTER1.companyB	101159575290188	Bitlocker Status	Off
COMPUTER1.companyB	101159575290188	SMART Drive Size	238GB
COMPUTER1.companyB	101159575290188	SMART Updated	01/14/2021
COMPUTER1.companyB	101159575290188	SMART Status	CAUTION
COMPUTER2.companyA	110275323879092	SMART Drive Model	ST500DM002-1BD142
COMPUTER2.companyA	110275323879092	Bitlocker Status	Off
COMPUTER2.companyA	110275323879092	SMART Drive Size	455GB
COMPUTER2.companyA	110275323879092	SMART Updated	01/14/2021
COMPUTER2.companyA	110275323879092	SMART Status	CAUTION

The table should end up looking like this:

agentguid	Model	Size	Updated	Status
101159575290188	PLEXTOR PX-256M5Pro ATA Device	238GB	01/14/2021	CAUTION
101159575290188	ST500DM002-1BD142	455GB	01/14/2021	CAUTION

I think my goal is to use either a PIVOT, UNPIVOT, or a CROSS, but I can't figure out how to make any of them work. Any help or guidance would be appreciated, thanks!

Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Charlieface, Jan 18 '21 at 21:49
Thank you for the referred link, I read through it, and I still am not sure if it applies, especially because the right-most cell (fieldValue), is dependent on the cell to it's left (fieldName). In addition, the answers provided in that guide are all numbers being added together, while mine are strings that can't be merged. Each one of the 3000+ computers being queried will have a different result. Is PIVOT even the right tool? I'm just not sure what would be easiest. — QuackMasterDan, Jan 18 '21 at 21:55

score 2 · Accepted Answer · answered Jan 18 '21 at 21:59

2

SELECT
  agentguid,
  MAX(CASE WHEN fieldName = 'SMART Drive Model' THEN fieldValue END)   AS Model,
  MAX(CASE WHEN fieldName = 'SMART Drive Size'  THEN fieldValue END)   AS Size,
  MAX(CASE WHEN fieldName = 'SMART Updated'     THEN fieldValue END)   AS Updated,
  MAX(CASE WHEN fieldName = 'SMART Status'      THEN fieldValue END)   AS Status
FROM
  vSystemInfoManual
GROUP BY
  agentguid

answered Jan 18 '21 at 21:59

MatBailie

83,401
18
103
137

And of course, one should generally include an ORDER BY clause since order of rows almost always matters. Let's also not forget the statement terminator. Encourage good habits. – SMor Jan 18 '21 at 22:18

Convert Rows to Columns based on contents? (PIVOT?)

1 Answers1