0

I'm attempting a join with a table that looks like this:

Table

I need to join on the distinct combination of SubTestCodes and TestCodes.

Is there a way to retrieve an output where the table is converted into long format (SubTestCodes becoming TestCodes)?

For this specific query there is no distinction between them.

A. Ganady
  • 347
  • 1
  • 3
  • 15
  • 4
    Go and yell at the goon that put a comma delimited list of values into a single field and make them fix it? – JNevill Nov 03 '17 at 17:11
  • I'm sure this question will get pounced on, and everyone will say "you shouldn't store data like this in a table". So, you shouldn't store data like this in a table...make another subcode table and a relationship between the testcode and subtestcode. – Jeremy Nov 03 '17 at 17:11
  • 1
    I should have mentioned I have read only access! Of course the true solution is to reformat the data. – A. Ganady Nov 03 '17 at 17:12
  • 1
    or...you could do a quick search and see that the question has been answered a thousand times over. Here's one to get started. https://stackoverflow.com/questions/15585632/how-to-convert-comma-separated-nvarchar-to-table-records-in-sql-server-2005 – Jeremy Nov 03 '17 at 17:14
  • What version of SQL Server? For SQL Server 2016+ you can use `STRING_SPLIT()` https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql – squillman Nov 03 '17 at 17:31
  • Is it REALLY a table? Sure it's not a view? – Paul Maxwell Nov 04 '17 at 02:22
  • Possible duplicate of [How to convert comma separated NVARCHAR to table records in SQL Server 2005?](https://stackoverflow.com/questions/15585632/how-to-convert-comma-separated-nvarchar-to-table-records-in-sql-server-2005) – Alberto Martinez Nov 04 '17 at 13:21

0 Answers0