How can I hide duplicate data?

Question

I am trying to hide the duplicate data from multiple rows, here is my current code and I have provided an image of the output, I need duplicates in columns LOGINID, EMAIL and USERGROUP to only show once. Also, how do I get the data from "ROLE" column into the "USERGROUP" column? So it looks like the last image? I am using SQL Server Management Studio. Any help would be really appreciated.

SELECT LTRIM(RTRIM(HOST0149.LOGINID)) AS LOGINID,
                CASE WHEN HOST0140.EMAIL = HOST0149.LOGINID THEN NULL
                     ELSE LTRIM(RTRIM(HOST0140.EMAIL)) END AS EMAIL,
                LTRIM(RTRIM(HOST0149.USERKEY)) AS USERGROUP, 
                LTRIM(RTRIM(HOST0150.ROLE)) AS ROLE
FROM HOST0149
  LEFT JOIN HOST0140 ON HOST0149.PERSONKEY = HOST0140.PERSONKEY
  LEFT JOIN HOST0151 ON HOST0140.PERSONKEY = HOST0151.PERSONKEY
  LEFT JOIN HOST0150 ON HOST0151.ROLEKEY = HOST0150.ROLEKEY
ORDER By LOGINID

your sample input image and output image are mismatch please post actual data without images OR create SQL fiddle — wiretext, Oct 19 '15 at 08:33

score 0 · Answer 1 · edited May 23 '17 at 12:29

You question is a little unclear, but I think you are wanting to concatenate the duplicate rows, rather than remove them from the database.

Assuming that is correct, you can use the xml features to do this (see: Concatenate many rows into a single text string?)

Select distinct LOGINID, 
                EMAIL
        (
            Select HOST0150.ROLE + char(13)+char(10) + AS [text()]
            From HOST0150
            Where HOST0151.ROLEKEY = HOST0150.ROLEKEY
            ORDER BY HOST0150.ROLEKEY
            For XML PATH ('')
        ) [roles]
    FROM HOST0149
LEFT JOIN HOST0140 ON HOST0149.PERSONKEY = HOST0140.PERSONKEY
LEFT JOIN HOST0151 ON HOST0140.PERSONKEY = HOST0151.PERSONKEY
LEFT JOIN HOST0150 ON HOST0151.ROLEKEY = HOST0150.ROLEKEY
ORDER By LOGINID

score 0 · Answer 2 · answered Oct 19 '15 at 08:42

0

Export your file into excel through export function then simply replace the data or add a check where duplicate data remove that row .. sorry for my English

answered Oct 19 '15 at 08:42

Ali Hamdani

17
7

How can I hide duplicate data?

2 Answers2