InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 15:55:29 GMT20http://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspx[quote][b]michael vessey (6/8/2012)[/b][hr][quote]If this was a geography forum, you would be the flat earth kid:w00t: [/quote][quote]Since you don't know anything about RDBMS, then get a copy of the simplest intro book I know -- [/quote]that kind of snooty reply doesn't serve the sql server central community well - the guy has asked a genuine question and lynn has replied with a valid response, which the OP has taken onboard and learned.not all of us have time to sit and study, some people get thrown in at the deep end, and the OP had the sense to ask the question.[/quote]Actually, while Lynn's response probably helped the OP solve his original question, it did nothing to educate him in the right way to manage the data. Celko's response, while "snooty" is probably the advice the OP needs.Fri, 08 Jun 2012 11:52:55 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspx[quote]If this was a geography forum, you would be the flat earth kid:w00t: [/quote][quote]Since you don't know anything about RDBMS, then get a copy of the simplest intro book I know -- [/quote]that kind of snooty reply doesn't serve the sql server central community well - the guy has asked a genuine question and lynn has replied with a valid response, which the OP has taken onboard and learned.not all of us have time to sit and study, some people get thrown in at the deep end, and the OP had the sense to ask the question.Fri, 08 Jun 2012 04:38:34 GMTMVDBAhttp://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspx[quote][b]Lynn Pettis (5/18/2012)[/b][hr]MODEL 01: name email A B C Daa john@test.com 0 0 1 1bb rick@test.com 0 1 0 1cc sally@test.com 0 1 1 0dd aha@test.com 1 1 0 0What would make better sense is to take the four columns: A,B,C,D and move them into a separate table:[code="plain"]MODEL 01ANameID Name EMail1 aa john@test.com 2 bb rick@test.com3 cc sally@test.com 4 dd aha@test.comNameID Mode1 C1 D2 B2 D3 B3 C4 A4 B[/code]This allows adding new modes by adding a row and deleting a mode by deleting a row.[/quote]I want to help! If you've mistakenly used MODEL 01 and you want to convert to Lynn's MODEL 01A, you can do it this way:[code="sql"]DECLARE @t TABLE (NameID INT IDENTITY, name VARCHAR(20), email VARCHAR(20), A INT, B INT, C INT, D INT)INSERT INTO @tSELECT 'aa','john@test.com',0, 0, 1, 1UNION ALL SELECT 'bb','rick@test.com',0, 1, 0, 1UNION ALL SELECT 'cc','sally@test.com',0, 1, 1, 0UNION ALL SELECT 'dd','aha@test.com',1, 1, 0, 0-- MODEL 01:SELECT * FROM @t-- MODEL 01A (table 2):SELECT NameID, ModeFROM @t CROSS APPLY ( VALUES (CASE A WHEN 1 THEN 'A' ELSE NULL END) ,(CASE B WHEN 1 THEN 'B' ELSE NULL END) ,(CASE C WHEN 1 THEN 'C' ELSE NULL END) ,(CASE D WHEN 1 THEN 'D' ELSE NULL END)) x(Mode)WHERE Mode IS NOT NULL[/code]That my friends, is my stupid SQL trick for this Friday afternoon!Fri, 08 Jun 2012 04:18:12 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspx[quote] Is there a construct in SQL Server to handles columns of a list data type? [/quote]If this was a geography forum, you would be the flat earth kid:w00t: Read any book -- and I mean ANY book -- on RDBMS. Look at First Normal Form, the basis of the entire relational model. Read Codd's 12 rules and look at the Information Principle in that list. Al relationships are shown by scalar values in the columns of rows of tables. Since you don't know anything about RDBMS, then get a copy of the simplest intro book I know -- http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905It is really good and I use it for classes that have students without any SQL or math background. Wed, 06 Jun 2012 15:42:42 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspxIt would be best to have one table with name and email, foreign keyed to another table that had the values as rows for each name and email. This would give the same observant performance, without having to parse data AND its scalable and requires NO data changes as you add more values to the range.That is afterall, what relational databases do best! :)Thu, 31 May 2012 13:13:36 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspxThanks Lynn. Good solution. I will follow that.Fri, 18 May 2012 13:40:20 GMTonlygoodoneshttp://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspxMODEL 01: name email A B C Daa john@test.com 0 0 1 1bb rick@test.com 0 1 0 1cc sally@test.com 0 1 1 0dd aha@test.com 1 1 0 0What would make better sense is to take the four columns: A,B,C,D and move them into a separate table:[code="plain"]MODEL 01ANameID Name EMail1 aa john@test.com 2 bb rick@test.com3 cc sally@test.com 4 dd aha@test.comNameID Mode1 C1 D2 B2 D3 B3 C4 A4 B[/code]This allows adding new modes by adding a row and deleting a mode by deleting a row.Fri, 18 May 2012 13:06:29 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspxThanks Lynn for the quick reply. Will there be any performance difference between the two. Meaning 1) having more columns in the table VS parsing a single column?Fri, 18 May 2012 12:57:22 GMTonlygoodoneshttp://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspxModel 1. Doesn't require the parsing of the data that Model 2 would.Fri, 18 May 2012 12:45:27 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspxHello every one, here I need your help in deciding which model is a good practice and easy to maintain: [b]MODEL 01: [/b]name email A B C Daa john@test.com 0 0 1 1bb rick@test.com 0 1 0 1cc sally@test.com 0 1 1 0dd aha@test.com 1 1 0 0 I have a table with columns A-D which are of the BIT data type. Each column represents a different scenario. To keep track of whether or not a particular type of email needs to be sent to the person (their row) 0 - indicates email sent and 1 - indicates, email needs to be sent out. As emails (different scenario's) are added to the system this model requires a new column to be added and widens the table. I’m wondering if the email type (A-D) could be stored in a list column like outline below: [b]MODEL 02: [/b] name email Xaa john@test.com 'A,B,C,D'bb rick@test.com 'B,D'cc sally@test.com 'C,D"dd aha@test.com 'A,B' In the above model, after the email is sent out for scenario A, it should dis appear from 'A,B,C,D' indication emails yet to be sent for B,C and D. Is there a construct in SQL Server to handles columns of a list data type? Would performance suffer when querying this table with the “LIKE” command? Is there another way to query this column, like a ListFind() function? Would I have to handle all maintenance to ensure that the list is always well formed from the application code? Should I just stick with the original model and deal with all of the columns per email? I’d rather not create another table in this case. Fri, 18 May 2012 12:39:41 GMTonlygoodones