0
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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.