Crosstab in SQL Server with a column that may have no data

Question

I've got a query in SQL Server that attempts to find duplicate combinations of columns. It looks like this:

SELECT SOC_NUM, CLS_NUM, CAT_NUM, QERROR, Sum(1) as CNT
FROM dbo.AS_Data_Disjoint_RPTS (nolock)
WHERE ReportName='NBN_Errors' AND QERROR IS NOT NULL
GROUP BY SOC_NUM, CLS_NUM, CAT_NUM, QERROR
ORDER BY SOC_NUM, CLS_NUM, CAT_NUM

It produces a dataset like this:

SOC_NUM         CLS_NUM      CAT_NUM        QERROR              CNT
25113              25         TX9AW          NBN_ID_ERROR       4
25113              25         WI913          NBN_ID_ERROR       4
25113              26         TX9AW          NBN_ID_ERROR       4
25113              26         WI913          NBN_ID_ERROR       4
25113              27         TX9AW          NBN_ID_ERROR       4
25113              27         WI913          NBN_ID_ERROR       4
25257               9         TX9AW          NBN_ID_ERROR       5
25257               9         TX9AW          No Error           1
25257              10         TX9AW          NBN_ID_ERROR       5
25257              10         TX9AW          No Error           1
25257              11         TX9AW          NBN_ID_ERROR       5
25257              11         TX9AW          No Error           1
25257              12         TX9AW          NBN_ID_ERROR       5
25257              12         TX9AW          No Error           1

As you can see from the groupings, SOC NUM 25113, CLS NUM 25 have no NTWRK IDs in common and would be ignored:

SOC_NUM         CLS_NUM      CAT_NUM        QERROR              CNT
25113              25         TX9AW          NBN_ID_ERROR       4
25113              25         WI913          NBN_ID_ERROR       4

However, SOC NUM 25257, CLS NUM 9 has records for NTWRK ID TX9AW that are both No Error and NBN ID ERROR:

SOC_NUM         CLS_NUM      CAT_NUM        QERROR              CNT
25257               9         TX9AW          NBN_ID_ERROR       5
25257               9         TX9AW          No Error           1

I need to build a PIVOT query that would look like this:

SOC_NUM    CLS_NUM        CAT_NUM       Error 1                   Error 2
25113        25            TX9AW        NBN ID ERROR              NULL
25113        25            WI913        NBN ID ERROR              NULL
25257         9            TX9AW        NBN ID ERROR              No Error

It would have all the SOC_NUM/CLS_NUM/CAT_NUM combos, not just the three above, I simplified the expected dataset for time/space constraints.

The purpose of this is to match the results against a large dataset and flag all records where there are 2 errors for any given SOC_NUM/CLS_NUM/CAT_NUM combo. I figured if I take the results of this PIVOT, I could just filter for any records where ERROR 2 is not NULL and I'd have all the combos I needed to find.

Can someone help me build the PIVOT query necessary to complete this task? I have ZERO understanding of PIVOT construction, I just can't wrap my head around it.

Answer 1

It looks like you just need to take out QERROR from the grouping, and use MIN and MAX to get two different values.

SELECT
  dd.SOC_NUM,
  dd.CLS_NUM,
  dd.CAT_NUM,
  MIN(dd.QERROR) AS [Error 1],
  NULLIF(MAX(dd.QERROR), MIN(dd.QERROR)) AS [Error 2]   -- give the max value unless it's the same as the min value
FROM dbo.AS_Data_Disjoint_RPTS dd
WHERE dd.ReportName = 'NBN_Errors'
  AND dd.QERROR IS NOT NULL
GROUP BY
  dd.SOC_NUM,
  dd.CLS_NUM,
  dd.CAT_NUM
HAVING COUNT(*) > 1
ORDER BY
  dd.SOC_NUM,
  dd.CLS_NUM,
  dd.CAT_NUM;