CODEWITHSUNDEEP
sqlsql-server
Nguyen Thanh Binh
Nguyen Thanh Binh

Reputation: 11

How to get values other from a string in SQL

I'm using the stuff function to get a list of ids. But there are some values that are duplicate:

----------
3180,3181,3182,3180,4180
----------
3183,3184,3184,4181

I just want to get the list of ids that are not duplicated, desired results:

----------
3181,3182,4180
----------
3183,4181

This my query: 

    SELECT oa.AssetId,
       oal.AssetLineId,
      SNIds = STUFF((SELECT ', ' + CAST(isn1.Id AS VARCHAR(MAX))
                     FROM dbo.InventoryOperation o1
                         INNER JOIN dbo.InventoryOperationAsset oa1 ON oa1.OperationId = o1.OperationId
                         INNER JOIN dbo.InventoryOperationAssetLine oal1 ON oal1.OperationAssetId = oa1.OperationAssetId
                         INNER JOIN dbo.InventoryOperationAssetLineSerialNumber ioalsn1 ON ioalsn1.OperationAssetLineId = oal1.OperationAssetLineId
                         INNER JOIN dbo.InventorySerialNumber isn1 ON isn1.Id = ioalsn1.SerialId
                     WHERE oa.AssetId = oa1.AssetId AND oal.AssetLineId = oal1.AssetLineId
                 FOR XML PATH('')),1,2,'')
FROM dbo.InventoryOperation o
    INNER JOIN dbo.InventoryOperationAsset oa ON oa.OperationId = o.OperationId
    INNER JOIN dbo.InventoryOperationAssetLine oal ON oal.OperationAssetId = oa.OperationAssetId
    INNER JOIN dbo.InventoryOperationAssetLineSerialNumber ioalsn ON ioalsn.OperationAssetLineId = oal.OperationAssetLineId
GROUP BY oa.AssetId, oal.AssetLineId;

Can anyone help me with this issue? Thanks in advance if anyone could help.

Upvotes: 1

Views: 71

Answers (2)

JRG
JRG

Reputation: 4177

Query that provides data to STUFF function should use following query to get only unique ids (e.g. 3181,3182,4180,3183,4181) and not the ones that have duplicates (e.g. 3180,3184)

  SELECT column_name
  FROM TABLE_NAME
  GROUP BY column_name
  HAVING COUNT(*) = 1;

Updating answer with STUFF function sample ...

SELECT STUFF(', ' + REPLACE((SELECT id + ', ' AS 'data()'
FROM stackoverflow
GROUP BY id
HAVING COUNT(*) = 1
FOR XML PATH('')), '', ''), 1, 2, '')

Sample Run enter image description here

Upvotes: 2

Yarince
Yarince

Reputation: 63

You could use 

SELECT DISTINCT columns FROM table;

That makes sure you only get non duplicates.

Upvotes: 2

Related Questions