select query using this format

Question

I have the data in this format

      NAME1 AMOUNT1 Internal
      NAME1 AMOUNT1 External
      NAME2 AMOUNT2 Internal
      NAME2 AMOUNT2 External

but now i need to extract the data in the below format using SQL-Server

    NAME1 AMOUNT1 Internal
                  External
    NAME2 AMOUNT2 Internal
                  External

Please suggest.

do you actually need empty rows to be returned ? if yes, that's not possible with sql server — Horia, Aug 14 '15 at 07:32
Hi, it is not empty rows, i want if same data is there in the next row then display as space — susmitha, Aug 14 '15 at 07:34
This is more of a presentation issue not typically and not easily solved in the sql. — larsts, Aug 14 '15 at 08:03

Stanislovas Kalašnikovas · Accepted Answer · 2015-08-14T08:18:03.970

You can achieve It by using ROW_NUMBER() and CASE in following:

SAMPLE DATA:

CREATE TABLE #t
(
   Name NVARCHAR(40),
   Amount NVARCHAR(40),
   Types NVARCHAR(40)
)
INSERT INTO #t VALUES
   ('NAME1', 'AMOUNT1', 'Internal'),
   ('NAME1', 'AMOUNT1', 'External'),
   ('NAME2', 'AMOUNT2', 'Internal'),
   ('NAME2', 'AMOUNT2', 'External')

QUERY

SELECT CASE WHEN rn > 1 THEN '' ELSE Name END AS Name,
       CASE WHEN rn > 1 THEN '' ELSE Amount END AS Amount,
       Types
FROM(
    SELECT Name, 
           Amount, 
           Types, 
           ROW_NUMBER() OVER (PARTITION BY Name, Amount ORDER BY Name) rn
    FROM #t
    ) x

OUTPUT

Name    Amount      Types
NAME1   AMOUNT1     Internal
                    External
NAME2   AMOUNT2     Internal
                    External

DEMO

You can test it: SQL FIDDLE

when i include row_number in my sql query i am not able to retrive data at all. Please suggest — susmitha, Aug 14 '15 at 09:16

select query using this format

1 Answers1