0

I am using SQL Server 2008 and inherited a database that did not use many to many. They instead used a comma-separated column. I have found how to link the comma-separated values to the name of the program. But I need a list of the programs and the offices they belong to, like this

OFFICE table:

ID  Name
--- ------
1   HQ
2   PA
3   CEO

PRG table:

ID  Name    Office Affected
--  ----    ---------------
A   PRG1    1,3
B   PRG2    2
C   PRG3    2,3
D   PRG4    1,2

Output that I need :

Name    Programs
----    ---------
HQ      PRG1, PRG4
PA      PRG2, PRG3, PRG4
CEO     PRG1, PRG3
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3139698
  • 1
  • You **really** need to split the comma separated values out before you go any further. – gvee Dec 27 '13 at 15:21
  • Can you change your table definition? – juergen d Dec 27 '13 at 15:21
  • http://explainextended.com/2009/06/23/in-with-a-comma-separated-list-sql-server/ – Praveen Prasannan Dec 27 '13 at 15:23
  • http://stackoverflow.com/questions/10553198/merge-multiple-rows-into-a-single-row/10553404#10553404 – SQLMason Dec 27 '13 at 15:24
  • http://stackoverflow.com/questions/10333319/sql-in-variable-query/10333589#10333589 – SQLMason Dec 27 '13 at 15:27
  • If you read my two answers, you can find **a** solution to your problem. However, as @gvee mentioned, you really need to get rid of the CVS column else your performance will be poor. – SQLMason Dec 27 '13 at 15:28
  • No, the data and table were inherited. The source code that uses the table (.net) was lost in a server crash. I cannot change the database without affecting the program. I am trying to get some information for other users. – user3139698 Dec 27 '13 at 15:29

1 Answers1

1

You can manage to do this. However, because storing lists in strings is a bad idea, I don't want to compound that by putting them back in a comma-delimited list. Instead, the following query produces the data in a more normalized form, with one row per office name and program:

select o.name, p.name as program_name
from prg p join
     office o
     on ','+p.OfficeAffected+',' like '%,'+cast(o.id as varchar(255)) + ',%';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786