0

I have 2 tables, category and subcategory, this query

select C.Description,s.Description
from category C
inner join SubCategory S on C.CategoryID =S.CategoryID 
where  C.IsActive=1 and S.IsActive=1

gives the following output:

enter image description here

But my output has to be as follows:

enter image description here

1
  • That can be accomplished on the presentation layer.
    – McNets
    May 21, 2019 at 14:17

3 Answers 3

1

You can accomplish that using ROW_NUMBER().

Take a look at the example below:

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp ( Description VARCHAR(100), SubDescription VARCHAR(100));

INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds2' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds3' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds4' );

INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds2' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds3' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds4' );

WITH Temp
AS (
    SELECT
        Description,
        SubDescription,
        ROW_NUMBER() OVER ( PARTITION BY Description
ORDER BY Description ) rownumber
    FROM #Temp
)
SELECT
    CASE WHEN Temp.rownumber = 1 THEN Description ELSE '' END Description,
    SubDescription
FROM Temp;

But I'd prefer doing that in the application layer.

2
  • Thank you for the timely help
    – Revathi
    May 22, 2019 at 6:26
  • Anytime! @Revathi, If my answer solved your problem, click the big checkbox to accept it as the answer. May 22, 2019 at 13:32
0

Rrequested result set can not be possible to get through sql. Sql server matches row from source to destination and build a result set. To achieve this task put row group on first column at application end.

-1
select C.Description,s.Description
from category C
inner join SubCategory S on C.CategoryID =S.CategoryID 
where  C.IsActive=1 and S.IsActive=1 

output

1
  • It doesn't seem the desired result.
    – McNets
    May 21, 2019 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.