Here is the key value table
Key value one rose one apple one Grapes
result showing like this
Key value one rose apple Grapes
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
This really is something that the display layer should handle. But it can be done in SQL Server. The below sample should show you the basic method.
DECLARE @SampleTable AS TABLE ( Keys VARCHAR(10) NOT NULL , Val VARCHAR(20) NOT NULL ); INSERT INTO @SampleTable (Keys, Val) VALUES ('one', 'rose') , ('one', 'apple') , ('one', 'grapes') , ('two', 'boxes') , ('two', 'jars') , ('three', 'ships'); SELECT Keys, Val FROM @SampleTable;
The Query, using ROW_NUMBER and PARTITION BY with a CASE statement to selectively hide the key name.
WITH CTE_Table AS ( SELECT S.Keys , S.Val , ROW_NUMBER() OVER (PARTITION BY S.Keys ORDER BY S.Keys, S.Val) AS GroupOrder FROM @SampleTable AS S ) SELECT Keys = CASE WHEN C.GroupOrder = 1 THEN C.Keys ELSE '' END , C.Val FROM CTE_Table AS C;