Databases
StackOverflow2013
Postsdbo
POTrouble getting distinct records
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble getting distinct records
    text
    copied!<p>I've got a table that holds IDs, with a alternate identifier.</p> <pre><code>ID| Alternate 1 | 100000000 1 | 8471 1 | 1248 2 | 578887518 2 | 8415 2 | 3574 3 | 100000000 3 | 1248 </code></pre> <p>There are two types of alternate identifiers.. 9 digit, and 4 digit. Every ID, has a 9 digit alternate, and then up to four different 4 digit ones.</p> <p>My issue is that the table is not unique. There exists cases similar to the above example IDs 1 and 3. There probably also exists situations, although I haven't found one yet, where the 9 digit identifiers match, but there are different 4 digit identifiers.. </p> <pre><code>ID | Alternate 5 | 200000000 5 | 1234 6 | 200000000 6 | 4567 </code></pre> <p>My goal is to create a table, and pivot on the alternate identifier value, however I'm currently getting two rows for the same user. </p> <p>Does someone know how I would go about fixing this?<br> Current code</p> <pre><code>SELECT ID, [THCI], [2101] , [2202], [2245] FROM (SELECT T1.[ID], T1.[IDENTIFIER], CASE WHEN T2.[MAIN_IDENT] IS NULL THEN 'THCI' ELSE T2.[MAIN_IDENT] END AS PIV FROM MYTABLE T1 LEFT OUTER JOIN TABLE2 AS T2 ON T2.[REFNO] = T1.[REFNO] WHERE (LEN(T2.[MAIN_IDENT]) = 4 OR T2.[MAIN_IDENT] IS NULL) AND (LEN(T1.[IDENTIFIER]) = 4 OR LEN(T1.[IDENTIFIER]) = 9) ) AS SourceTable PIVOT (MAX([IDENTIFIER]) FOR PIV IN ([THCI], [2245], [2202], [2101],[2345],[2303])) AS MYPIV </code></pre> <p>EDIT: Expected output would be. Note also, it's four alternate identifiers, not three.. Doesn't change much though</p> <pre><code>[ID] | [THCI] | [2245] | [2202] | [2101] | [2345] 1 | 100000000 | 4817 | 6824 | NULL | 1587 2 | 845279514 | 2359 | 7847 | 1234 | NULL 3 | 415784521 | 2415 | NULL | NULL | 8747 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload