| AnimalId | Date | Remark | Number | PA | PCountA | PB | PCountB | PC | PCountC | PD | PCountD |
|---|---|---|---|---|---|---|---|---|---|---|---|
| AN001 | 2022-01-25 | LR | 22025002 | coli | 100 | Strep ag | 100 | Strep s | 100 | Staph a | 100 |
| AN001 | 2022-01-25 | 12:10am | 22025002 | coli | 300 | Staph a | 101 | Strep s | 100 | Strep ag | 100 |
| AN002 | 2022-01-25 | 1:50am | 22025002 | coli | 1500 | Staph a | 1100 | null | null | null | null |
| AN003 | 2022-01-25 | 1:20pm | 22025002 | Haem | TNTC | Staph a | 320 | Strep s | TNTC | Strep ag | 100 |
| AN004 | 2022-01-25 | 1:10pm | 22025002 | coli | 50 | Staph s | 400 | Staph a | 100 | Strep ag | 100 |
| AN004 | 2022-01-25 | 1:50pm | 22025002 | 0 growth | null | null | null | null | null | null | null |
I have a pivoted view as shown in the above table. It has Pathogens e.g PA found in an animal sample AnimalId and the pathogen counts e.g PCountA. There are pathogens Staph a and Strep ag that when found, the pathogen and its result should come first/listed in the first column. For example,in the second row AN001, Staph a under column PB and it's count under column PCountB should shift to columns PA and PCountA, i.e it should be the starting pathogen.
In cases where we have both pathogens Staph a and Strep aglike in the first row, Staph a should start followed by Strep ag then the rest.
How do I achieve this in SQL Server?
Note: I tried CASE ordering by Pathogen before pivoting but it didn't work out.
See below.
SELECT AnimalId, Date, Remark, Number,
PA, PCountA, PB, PRCountB, PC, PCountC,
PD, PCountD
FROM (
SELECT AnimalId, Date, Remark, Number, Col, Val
FROM (
SELECT AnimalId, Date, Remark, Number, Pathogen,
PathogenResult,
CASE
WHEN PathogensCount = 1 THEN 'PA'
WHEN PathogensCount = 2 THEN 'PB'
WHEN PathogensCount = 3 THEN 'PC'
WHEN PathogensCount = 4 THEN 'PD'
END AS PathogenType
,
CASE
WHEN PathogensCount = 1 THEN 'PCountA'
WHEN PathogensCount = 2 THEN 'PCountB'
WHEN PathogensCount = 3 THEN 'PCountC'
WHEN PathogensCount = 4 THEN 'PCountD'
END AS PathogenResultType
FROM (
SELECT * FROM (
SELECT * FROM (
SELECT s.AnimalId, s.SampleDate AS Date, s.SampleRemark AS Remark, o.Name AS Pathogen,
CASE WHEN o.RequiresCount = 0 THEN NULL
WHEN sr.NewCount = 999999 THEN 'TNTC'
WHEN t.Name LIKE '%tank%' THEN CAST(sr.NewCount * 10 AS nvarchar)
ELSE CAST(sr.NewCount * 100 AS nvarchar)
END AS PathogenResult,
a.Number,
COUNT(
CASE WHEN o.RequiresCount = 0 THEN NULL
WHEN sr.NewCount = 999999 THEN 'TNTC'
WHEN t.Name LIKE '%tank%' THEN CAST(sr.NewCount * 10 AS nvarchar)
ELSE CAST(sr.NewCount * 100 AS nvarchar)
END) OVER(PARTITION BY s.AnimalId) AS CountOfPathogenResult
FROM Table_s s
INNER JOIN Table_sr sr ON sr.ATSId = s.Id
LEFT JOIN Table_o o ON o.Id = sr.NOId
INNER JOIN Table_sp sp ON sp.Id = sr.ATSPId
INNER JOIN Table_a a ON a.Id = sp.AId
INNER JOIN Table_ag ag ON ag.Id = sr.AgId
INNER JOIN Table_ats ats ON ats.AId = a.Id AND ats.Id = sp.ATId
INNER JOIN Table_t t ON t.Id = ats.TId
WHERE o.Name IS NOT NULL
GROUP BY a.Id, s.AnimalId, s.Date, s. SampleRemark, o.Name,
o.RequiresCount, t.Name, sr.NewCount, a.Number, ag.Name
) as A
WHERE CountOfPathogenResult > 0 AND Pathogen <> 'No Growth'
UNION
SELECT * FROM (
SELECT s.AnimalId, s.SampleDate AS Date, s.SampleRemark AS Remark,
o.Name AS Pathogen,
CASE WHEN o.RequiresCount = 0 THEN NULL
WHEN sr.NewCount = 999999 THEN 'TNTC'
WHEN t.Name LIKE '%tank%' THEN CAST(sr.NewCount * 10 AS nvarchar)
ELSE CAST(sr.NewCount * 100 AS nvarchar)
END AS PathogenResult,
a.Number,
COUNT(
CASE WHEN o.RequiresCount = 0 THEN NULL
WHEN sr.NewCount = 999999 THEN 'TNTC'
WHEN t.Name LIKE '%tank%' THEN CAST(sr.NewCount * 10 AS nvarchar)
ELSE CAST(sr.NewCount * 100 AS nvarchar)
END) OVER(PARTITION BY s.AnimalId) AS CountOfPathogenResult
FROM Table_s s
INNER JOIN Table_sr sr ON sr.ATSId = s.Id
LEFT JOIN Table_o o ON o.Id = sr.NOId
INNER JOIN Table_sp sp ON sp.Id = sr.ATSPId
INNER JOIN Table_a a ON a.Id = sp.AId
INNER JOIN Table_ag ag ON ag.Id = sr.AgId
INNER JOIN Table_ats ats ON ats.AId = a.Id AND ats.Id = sp.ATId
INNER JOIN Table_t t ON t.Id = ats.TId
WHERE o.Name IS NOT NULL
GROUP BY a.Id, s.AnimalId, s.Date, s. SampleRemark, o.Name,
o.RequiresCount, t.Name, sr.NewCount, a.Number, ag.Name
) as B
WHERE CountOfPathogenResult = 0 AND Pathogen = 'No Growth' OR CountOfPathogenResult = 0 AND Pathogen = 'Contaminated'
) AS C
ORDER BY CASE WHEN Pathogen = 'Staph aureus' THEN 1
WHEN Pathogen = 'Strep. ag.' THEN 2 ELSE 3
END, Pathogen
OFFSET 0 ROWS
) AS ResultsA
GROUP BY AnimalId, Date, Remark,Number, PathogenResult, Pathogen, PathogensCount) AS source cross apply (
SELECT PathogenType, Pathogen
UNION ALL
SELECT PathogenResultType, CAST(PathogenResult AS varchar(45))
) ResultsB (Col, Val)
) ResultsC
PIVOT (MAX(Val) FOR Col IN (
[PA],
[PCountA],
[PB],
[PaCountB],
[PC],
[PaCountC],
[PD],
[PaCountD],
[PathogenE])
) AS PivotResults;
GO
Expected Results
| AnimalId | Date | Remark | Number | PA | PCountA | PB | PCountB | PC | PCountC | PD | PCountD |
|---|---|---|---|---|---|---|---|---|---|---|---|
| AN001 | 2022-01-25 | LR | 22025002 | Staph a | 100 | Strep ag | 100 | coli | 100 | Strep s | 100 |
| AN001 | 2022-01-25 | 12:10am | 22025002 | Staph a | 101 | Strep ag | 100 | coli | 300 | Strep s | 100 |
| AN002 | 2022-01-25 | 1:50am | 22025002 | Staph a | 1100 | coli | 1500 | null | null | null | null |
| AN003 | 2022-01-25 | 1:20pm | 22025002 | Staph a | 320 | Strep ag | 100 | Haem | TNTC | Strep s | TNTC |
| AN004 | 2022-01-25 | 1:10pm | 22025002 | Staph a | 100 | Strep ag | 100 | coli | 50 | Staph s | 400 |
| AN004 | 2022-01-25 | 1:50pm | 22025002 | No growth | null | null | null | null | null | null | null |
