CODEWITHSUNDEEP
sqlsql-servert-sql
Navyseal
Navyseal

Reputation: 901

Combine records and show in a different columns

I have written a SQL which gives me a dataset.

    SELECT [Id]
      --,s1.[CustomerId]
      ,[Date]
      ,dc.ColumnName
      ,dc.ColumnId
      ,[RuleId]
     -- ,[RuleExceptionCount]
      --,[TotalRowCount]
      ,CAST((TotalRowCount - RuleExceptionCount)/CAST(TotalRowCount AS FLOAT) * 100 AS DECIMAL(6,2)) AS [FailurePercentage]
  FROM [ASMPredictiveAnalytics].[dbo].[DQHistory] AS s1
  JOIN dbo.DQColumns dc ON dc.ColumnId = s1.ColumnId
  WHERE Date = (SELECT MAX(date)  FROM
  dbo.DQHistory) AND s1.CustomerId=@CustomerId

The result looks like this

Looks right now

I wanted to result to look like this

[Want to make it this way[2]

Basically transpose the records for "Summary" and "Resolution notes" into a single record by adding another column called "Quality" and for the rest of the other "Columnnames", need to show the "Quality" as "N/A".

Upvotes: 1

Views: 33

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

You should modify your query like below:

SELECT 
     MIN([Id]) as [Id],
     [Date],
     [ColumnName],
     [ColumnId], 
     COALESCE(CAST(MIN(FailurePercentage) as varchar),'N/A') as FailurePercentage,
     COALESCE(cast(MIN(Quality) as varchar),'N/A') as Quality
 FROM
   (SELECT 
              [Id]
              --,s1.[CustomerId]
              ,[Date]
              ,ColumnName
              ,ColumnId
              -- ,[RuleExceptionCount]
              --,[TotalRowCount] 
              ,CASE WHEN [RuleId]=1 THEN CAST((TotalRowCount - RuleExceptionCount)/CAST(TotalRowCount AS FLOAT) * 100 AS DECIMAL(6,2)) ELSE NULL END as [FailurePercentage]
              ,CASE WHEN [RuleId]=2 THEN CAST((TotalRowCount - RuleExceptionCount)/CAST(TotalRowCount AS FLOAT) * 100 AS DECIMAL(6,2)) ELSE NULL END as [Quality] 
       FROM [ASMPredictiveAnalytics].[dbo].[DQHistory] AS s1
        JOIN dbo.DQColumns dc ON dc.ColumnId = s1.ColumnId
        WHERE Date = (SELECT MAX(date)  FROM
        dbo.DQHistory) AND s1.CustomerId=@CustomerId
    ) T
    GROUP BY [Date],[ColumnName],[ColumnId]

Explanation:

Since you were doing same calculations for failure percentage and Quality, I've put them as same inside the inner query, but one of them is NULL based on the value of RuleId column.

In outer query we've simply grouped everything and calculate the MIN(you can use any aggregate function here) not NULL value, and replace that value with 'N/A' in case it is still NULL

Upvotes: 1

Related Questions