Sql-server – Get distinct category columns with multiple subcategory columns mapped in table

querysql server

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

Best Answer

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.

Related Question