SQL Server - pull from Comma Delimited field?
consists in db:
-Table A
--1
--2
--3
-Table B
--dog,cat,bird,fish
--dog,cat,bird,fish
--dog,cat,bird,fish
--horse,cow,sheep
How it's interpreted:
A.1 = B.dog
A.2 = B.cat
A.3 = B.bird
A.2 = B.cow
Looking for a query that will result in showing the text from Table B that matches the corresponding smallint from Table A. (The text value will differ a lot in query based in a value in Table C.) (In other words, everything before the first comma will match with A.1 ; everything in between the first and second comma will match 2 ; etc....)
Sorry for any confusion. I've done so much searching for how to do this, that I can't explain it. LOL
Update:I did not create the database and only have 'read' access, so have no control over how info is stored. I agree that there's better ways to store the data.
It's for work, not curiosity.
We have MS SQL Server 2008.
There's 3 Tables and a 'key' that ties them together. So, I'd say something to the effect of:
Select 'a.column', 'a.column'
from TableA a
JOIN TableB b
on a.key = b.key
JOIN TableC c
on b.key = c.key
(TableA values will change for rows of TableC ; TableB consists the values for each 'key' in TableC ; so, TableC = the value of TableA, which needs to be shown as the corresponding comma value in TableB.)
Still confusing?
Comments
I'm a bit confused.
How can A.2 = B.cat and A.2 = B.cow? Or do you mean A.2 = {cat,cow}?
Why is the data stored in comma delimited format? That is just so wrong. That's not how you store data in a database!
Is this a database of your own design? If so, rethink the design, don't try to solve this problem.
Or is this a homework question? If so, I guess you are stuck. Look for functions for splitting strings. Which flavor of SQL? If it MS SQL Server, I think you'll have to roll your own split function. See SO article below for some solutions to doing this. If you *really* have to.
ETA: Yes, still confusing.
I'm not sure what you are doing here:
Select 'a.column', 'a.column'
You're trying to select "something" from b, not a aren't you?
Are the joins right? TableB has a key column linking back to A, and TableC has a key column linking back to B? Or does TableC have the index into the comma delimited column in B?
I don't think we can really help without a more detailed description of the relevant columns in A, B and C.
And..you have my permission to shoot the person who put comma delimited fields in your DB in the first place ;-)
I suspect the ghost of Codd would want to rise from its grave and bitchslap whoever designed the database...a repeating-items list stuffed into a single column? Big no-no... Frankly, I can't think of an inelegant (let alone an elegant one) way to do this...sounds more like a job for a scripting language, cursor and string-manipulation functions.