SQL Remove Duplicate Records

Question

I have the following query:

SELECT
   CAST(t. NAME AS CHAR(12)) AS [ TABLE ], 
   CAST(c. NAME AS CHAR(20)) AS [ COLUMN ],
   CAST(d. NAME AS CHAR(9)) AS [ DATA type ], 
   CASE WHEN d. NAME IN ('char', 'varchar') THEN
     STR (c.max_length, 6, 0)
   ELSE
     ''
   END AS [ Length ],
   CASE WHEN d. NAME IN ('numeric', 'decimal') THEN
     STR (c. PRECISION, 9, 0)
   ELSE
     ''
   END AS [ PRECISION ],
   CASE WHEN d. NAME IN ('numeric', 'decimal') THEN
     STR (c.scale, 5, 0)
   ELSE
     ''
   END AS [ Scale ],
   CASE c.is_nullable
     WHEN 0 THEN
       'not null'
     ELSE
       ''
     END AS [ Nullable ]
FROM
   sys. COLUMNS AS c
JOIN sys. TABLES AS t ON c.object_id = t.object_id
JOIN sys.types AS d ON c.system_type_id = d.system_type_id
ORDER BY
   1,
   column_id;

Which gives me something like this:

    Table   | Column       | Data type|
    __________________________________
    Customer|  CustomerCode|  int     |  
    Customer| LastName     | varchar  | 
    Customer|  FirstName   |  varchar | 
    Customer|  AreaCode    |  char    |
    Employee|  Title       |  char    |
    Employee|  LastName    |  varchar |
    Employee|  FirstName   |  varchar |

However, i want to display the repeated words in Table only once, like this:

    Table   | Column       | Data type|
    __________________________________
    Customer|  CustomerCode|  int     |  
            | LastName     | varchar  | 
            |  FirstName   |  varchar | 
            |  AreaCode    |  char    |
    Employee|  Title       |  char    |
            |  LastName    |  varchar |
            |  FirstName   |  varchar |

I've been trying for hours now, but i cannot seem to get it right. Please help!!

Stanislovas Kalašnikovas · Accepted Answer · 2015-10-09T10:29:27.650

In SQL-Server you can do It in following:

select case when rn > 1 then '' else [Table] end as [Table], 
       [column],
       [data type]
from(
    select  [column], 
            [type], 
            row_number() over(partition by [Table] order by [Table]) rn
    from YourTable
) x

With your provided sample will be something like:

SELECT CASE WHEN rn > 1 THEN '' ELSE [TABLE] END AS [TABLE], 
       [COLUMN], [DATA type], [Length], [PRECISION], [Scale], [Nullable]
FROM(
    SELECT
       CAST(t. NAME AS CHAR(12)) AS [TABLE], 
       CAST(c. NAME AS CHAR(20)) AS [COLUMN], CAST(d. NAME AS CHAR(9)) AS [DATA type], 
    CASE WHEN d. NAME IN ('char', 'varchar') THEN
       STR (c.max_length, 6, 0)
    ELSE
       ''
    END AS [Length], CASE
    WHEN d. NAME IN ('numeric', 'decimal') THEN
       STR (c. PRECISION, 9, 0)
    ELSE
       ''
    END AS [PRECISION], CASE
    WHEN d. NAME IN ('numeric', 'decimal') THEN
       STR (c.scale, 5, 0)
    ELSE
       ''
    END AS [Scale], CASE c.is_nullable
    WHEN 0 THEN
       'not null'
    ELSE
       ''
    END AS [Nullable],
    ROW_NUMBER() OVER(PARTITION BY CAST(t. NAME AS CHAR(12)) ORDER BY CAST(t. NAME AS CHAR(12))) rn,
    [column_id]
    FROM
       sys. COLUMNS AS c
    JOIN sys. TABLES AS t ON c.object_id = t.object_id
    JOIN sys.types AS d ON c.system_type_id = d.system_type_id
)x

SQL Remove Duplicate Records

1 Answers1