• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

GROUP BY WITH CUBE Issue

I am having a problem with a query in SQL Server 2008R2.

 
SELECT T0.CardCode, T2.ItmsGrpNam
FROM OITM T0
INNER JOIN OITB T2 ON T0.ItmsGrpCod=T2.ItmsGrpCod
GROUP BY  T0.CardCode, T2.ItmsGrpNam  WITH CUBE

Open in new window


It yields this result set that looks like this:

CardCode | ItmsGrpNam
BrandX     | BrandX A
 (NULL)    |  BrandX A
BrandX     | BrandX B
 (NULL)    |  BrandX B

I am trying to get this result set:

CardCode | ItmsGrpNam
BrandX     | BrandX A
BrandX     |  BrandX B

OR this one:

CardCode | ItmsGrpNam
BrandX     | BrandX A
(NULL)      |  BrandX B

Can someone show me how to properly structure this query to achieve the desired result? Thanks.


0
Candidochris
Asked:
Candidochris
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remove the "WITH CUBE" to achieve the first alternative.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if really you needed the second alternative, you would need something like:

; with data as (
SELECT T0.CardCode, T2.ItmsGrpNam
, ROW_NUMBER() OVER (PARTITION BY T0.CardCode ORDER BY T2.ItmsGrpNam) rn
FROM OITM T0
INNER JOIN OITB T2 ON T0.ItmsGrpCod=T2.ItmsGrpCod
GROUP BY  T0.CardCode, T2.ItmsGrpNam 
)
SELECT CASE WHEN rn = 1 THEN CardCode ELSE NULL END CardCode, ItmsGrpNam
  FROM data
 ORDER BY CardCode, rn 

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT CardCode, ItmsGrpNam FROM
(SELECT '123' AS CardCode, 'ABC' AS ItmsGrpNam
UNION
SELECT '234' AS CardCode, 'ERT' AS ItmsGrpNam
UNION
SELECT '233' AS CardCode, 'ERT' AS ItmsGrpNam
UNION
SELECT '233' AS CardCode, 'RTT' AS ItmsGrpNam) A
GROUP BY  CardCode, ItmsGrpNam
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now