0

How can I print next values 'aa, bb, cc, dd, ee' from DB column, like that: 'aa-bb, aa-cc, aa-dd, aa-ee, etc'?

NOTE: If I have, for example following value: 'aa-bb', so 'bb-aa' should be skipped out

My TSQL Example doesn't work:

select l.SURNAME + '-' + l1.SURNAME
from LECTURERS l cross join LECTURERS l1 
where l.CITY = 'Kyiv' and l.SURNAME <> l1.SURNAME and LEFT(l1.SURNAME, 
charindex('-', l1.SURNAME)) <> l.SURNAME

Prints me following 'aa-bb, aa-cc, aa-dd, aa-ee', but how can I except 'aa-bb' <> 'bb-aa'

Денис Щербина
  • 1
  • 2
  • What happens if you change `l.SURNAME <> l1.SURNAME` to `l.SURNAME < l1.SURNAME`? – HABO Dec 02 '18 at 22:50
  • If you have a column that stores data like this, then you have a flaw in your database design and you should fix it. For more information, read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** – Zohar Peled Dec 03 '18 at 06:33
  • @ZoharPeled Based on the query I _think_ the question is about creating all possible pairs of `Lecturers` without duplicates, i.e. "Fred & Wilma" is equivalent to "Wilma & Fred" so only one of the pairs should be included in the results. It doesn't appear to be a case of horribly denormalized data. Some additional clarification from the OP would certainly help. – HABO Dec 03 '18 at 13:26
  • @HABO Perhaps I've misread the question. We will have to wait for clarification, as you wrote. – Zohar Peled Dec 03 '18 at 15:40

0 Answers0