CODEWITHSUNDEEP
sql-server-2008
Flavius Crişan
Flavius Crişan

Reputation: 3

SQL Server GROUP BY duplicate values

I have a NOTOK table that containts idproductype and idcause columns and a query that looks like this

SELECT pt.Name, c.Description 
FROM NOTOK n
JOIN ProductType pt ON n.IDProductType = pt.ID
JOIN Cause c ON n.IDCause=c.ID

That returns the following dataset

PRODUCTTYPE        CAUSE
productType1            cause1
productType1            cause1
productType1            cause1
productType1            cause2
productType1            cause2
productType1            cause3
productType2            cause1
productType2            cause1
productType3            cause3
productType3            cause3
productType3            cause1

Now, i'd like to group this by the ProductType-column and put the most common two causes in different column, so the results is as following

PRODUCTTYPE       CAUSE1       CAUSE2
productType1       cause1            cause2
productType2       cause1            NULL
productType3       cause3            cause1

How would i do that?

Upvotes: 0

Views: 86

Answers (2)

Lucero
Lucero

Reputation: 60190

Based on the comment I made to the now-deleted answer by Amirreza about not requiring PIVOT, here's an example based on TI's answer but without PIVOT.

Note that this is just for illustration, the PIVOT-based solution yields a more efficient query plan.

WITH cteCause AS (
 SELECT *, 
   ROW_NUMBER() OVER ( PARTITION BY product_type ORDER BY n DESC ) o
 FROM (
  SELECT product_type, cause, COUNT(1) n
  FROM notok
  GROUP BY product_type, cause
 ) t
)
SELECT
  t.product_type,
  (SELECT c.cause FROM cteCause c WHERE c.product_type=t.product_type AND c.o=1) cause1,
  (SELECT c.cause FROM cteCause c WHERE c.product_type=t.product_type AND c.o=2) cause2
 FROM (SELECT DISTINCT product_type FROM notok) t

(Fiddle is here.)

Upvotes: 1

T I
T I

Reputation: 9933

You can use a cte to get the most common and use ROW_NUMBER to provide a way of getting the top 2 causes per product. This can then be pivoted to the result set you require.

WITH cte AS
(
 SELECT *, 
   ROW_NUMBER() OVER ( PARTITION BY product_type ORDER BY n DESC ) o
 FROM (
  SELECT product_type, cause, COUNT(1) n
  FROM notok
  GROUP BY product_type, cause
 ) t
)

SELECT product_type, MAX([1]), MAX([2])
FROM cte
PIVOT (
  MAX(cause)
  FOR o IN ([1],[2])
) p
GROUP BY product_type

demo

Upvotes: 2

Related Questions