1

Here is the key value table

Key  value
one  rose
one  apple
one  Grapes

result showing like this

Key value
one rose
    apple
    Grapes
2
  • That kind of task belongs to the client application and not to the relational enginge. I advise against even trying that. Feb 14 '17 at 12:13
  • Using your key value table (and nothing else), I don't see how you can make sure the values come out as rose, apple, Grapes. There is nothing to force that order Feb 14 '17 at 12:14
1

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.

The Setup

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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