CODEWITHSUNDEEP
sqlsql-serverpivot
Santamma
Santamma

Reputation: 3

How to group some records by name and pivot some values

I am working on a SQL Query to group the results of a View by Id to have only one Row per Id with a maximum of three pivoted results and keeping some columns static : TestCaseId, TestName, Test Case Num, Owner

Actually this is the Query i Created to get the Desired output but is now working as expected because the MAX is always retriving the max value so i am getting only one row but the pivoted values are repeated to the right. 

SELECT DISTINCT  TBL1.[TestName], TBL1.[Test Case Num], TBL1.[Owner], MAX(TBL1.[Browser]) as 'Column1', MAX(TBL1.[Run Date]) as 'Column2', MAX(TBL1.[Status]) as 'Column3', MAX(TBL1.[Duration]) as 'Column4', MAX(TBL1.[ErrorMsg]) as 'Column5', MAX(TBL2.[Browser]) as 'Column6', MAX(TBL2.[Run Date]) as 'Column7', MAX(TBL2.[Status]) as 'Column8', MAX(TBL2.[Duration]) as 'Column9', MAX(TBL2.[ErrorMsg]) as 'Column10', MAX(TBL3.[Browser]) as 'Column11' , MAX(TBL3.[Run Date]) as 'Column12', MAX(TBL3.[Status]) as 'Column13', MAX(TBL3.[Duration]) as 'Column14', MAX(TBL3.[ErrorMsg]) as 'Column15' 
FROM (SELECT DISTINCT T1.[TestCaseId], T1.[TestName], T1.[Test Case Num], T1.[Owner], T1.[Browser], T1.[Run Date], T1.[Status], T1.[Duration], T1.[ErrorMsg]
FROM [TestRunner].[dbo].RunsRawResults T1) TBL1
cross apply (SELECT DISTINCT T2.[TestCaseId], T2.[Browser], T2.[Run Date], T2.[Status], T2.[Duration], T2.[ErrorMsg]
            FROM [TestRunner].[dbo].RunsRawResults T2
            WHERE T2.[TestCaseId] = TBL1.[TestCaseId] AND T2.[Run Date]  TBL1.[Run Date]) TBL2
cross apply (SELECT DISTINCT T3.[TestCaseId], T3.[Browser], T3.[Run Date], T3.[Status], T3.[Duration], T3.[ErrorMsg]
            FROM [TestRunner].[dbo].RunsRawResults T3
            WHERE T3.[TestCaseId] = TBL2.[TestCaseId] AND T3.[Run Date]  TBL2.[Run Date] AND  T3.[Run Date]  TBL1.[Run Date]) TBL3
            GROUP BY TBL1.[TestCaseId], TBL1.[TestName], TBL1.[Test Case Num], TBL1.[Owner]

Input - Raw Data (Comes from the RunRawResults View) enter image description here Desired and Pivoted Output

enter image description here

Upvotes: 0

Views: 56

Answers (1)

SqlZim
SqlZim

Reputation: 38063

Using a common table expression (cte) and row_number() we can simplify the identification and order of multiple run dates. This also lets us skip using distinct and group by.

Switching to outer apply lets us include results where there are less than 3 runs per TestCaseId.

;with cte as (
  select *
    , rn = row_number() over (
        partition by TestCaseId
        order by [Run Date]
        )
  from TestRunner.dbo.RunsRawResults
)
select 
    tbl1.TestName
  , tbl1.[Test Case Num]
  , tbl1.Owner
  , tbl1.Browser
  , [Run Date]_tbl1 = tbl1.[Run Date]
  , Status_tbl1     = tbl1.Status
  , Duration_tbl1   = tbl1.Duration
  , ErrorMsg_tbl1   = tbl1.ErrorMsg
  , Browser_tbl2    = tbl2.Browser
  , [Run Date]_tbl2 = tbl2.[Run Date]
  , Status_tbl2     = tbl2.Status
  , Duration_tbl2   = tbl2.Duration
  , ErrorMsg_tbl2   = tbl2.ErrorMsg
  , Browser_tbl3    = tbl3.Browser
  , [Run Date]_tbl3 = tbl3.[Run Date]
  , Status_tbl3     = tbl3.Status
  , Duration_tbl3   = tbl3.Duration
  , ErrorMsg_tbl3   = tbl3.ErrorMsg
from cte as tbl1
  outer apply (
    select 
        i.[Run Date]
      , i.Status
      , i.Duration
      , i.ErrorMsg
    from cte as i
    where i.TestCaseId = tbl1.TestCaseId 
      and i.rn = 2
   ) as tbl2
  outer apply (
    select 
        i.[Run Date]
      , i.Status
      , i.Duration
      , i.ErrorMsg
    from cte as i
    where i.TestCaseId = tbl1.TestCaseId 
      and i.rn = 3
   ) as tbl3
where tbl1.rn = 1

Upvotes: 0

Related Questions