InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 08:29:37 GMT20http://www.sqlservercentral.com/Forums/Topic765065-338-1.aspx[quote][b]steven (8/4/2009)[/b][hr]Thanks it worked for me, but I don't know how to mark your reply as Answer. can you tell me?[/quote]I don't think you can in this forum, well, just saying it will help people who are looking for the same answer.Thanks for the feedback Steven, have a nice day,Tue, 04 Aug 2009 13:59:15 GMTJ-F Bergeronhttp://www.sqlservercentral.com/Forums/Topic765065-338-1.aspxThanks it worked for me, but I don't know how to mark your reply as Answer. can you tell me?Tue, 04 Aug 2009 13:53:48 GMTsteven-733381http://www.sqlservercentral.com/Forums/Topic765065-338-1.aspx[quote][b]steven (8/4/2009)[/b][hr]Hi,I have a situation where I have bunch of 7-digit Encoded text in database like 0304182, now here First two characters represent Product i.e. 03Next two character represent Class i.e. 04 andLast thress characters represent SubClass i.e. 182Now I want to fill thress dropdowns (i.e. Product, Class, SubClass) with the distinct value from the database, where Subclass will populate on Class selection and Class will populate on Product selection.So can anyone suggest me the SQL query to get it to work ? Hope you understand the situation. I am using SQL server 2005.[/quote]Hi Steven,I built this query for you, check it out, see if it does what you are looking for.[code]USE tempdbGODROP TABLE ProductCREATE TABLE Product ( ID INT IDENTITY ( 1 , 1 ), EncodedProduct VARCHAR(7))INSERT INTO Product (EncodedProduct)SELECT '0101001'UNION ALLSELECT '0201001'UNION ALLSELECT '0301001'UNION ALLSELECT '0301002'UNION ALLSELECT '0102001'UNION ALLSELECT '0202001'UNION ALLSELECT '0302001'UNION ALLSELECT '0303001'UNION ALLSELECT '0401001'UNION ALLSELECT '0504001'UNION ALLSELECT '0101010'UNION ALLSELECT '0102010'UNION ALLSELECT '0103010'UNION ALLSELECT '0203010'--This fills your PrdCode comboSelect distinct PrdCode from (SELECT substring(EncodedProduct,1,2) AS PrdCode, Substring(EncodedProduct,3,2) AS PrdClass, Substring(EncodedProduct,5,3) AS PrdSubClassFROM Product) as PrdGO--This fills your PrdClass combo based on the PrdCode selection (@PrdCode)Declare @PrdCode varchar(2)set @PrdCode = '01'select distinct PrdClass from (SELECT substring(EncodedProduct,1,2) AS PrdCode, Substring(EncodedProduct,3,2) AS PrdClass, Substring(EncodedProduct,5,3) AS PrdSubClassFROM Product) as Prdwhere PrdCode = @PrdCodeGODeclare @PrdCode varchar(2), @PrdClass varchar(2)SET @PrdCode = '03'SET @PrdClass = '01'select distinct PrdSubClass from (SELECT substring(EncodedProduct,1,2) AS PrdCode, Substring(EncodedProduct,3,2) AS PrdClass, Substring(EncodedProduct,5,3) AS PrdSubClassFROM Product) as Prdwhere PrdCode = @PrdCodeAND PrdClass = @PrdClass[/code]Tue, 04 Aug 2009 13:32:29 GMTJ-F Bergeronhttp://www.sqlservercentral.com/Forums/Topic765065-338-1.aspxHi,I have a situation where I have bunch of 7-digit Encoded text in database like 0304182, now here First two characters represent Product i.e. 03Next two character represent Class i.e. 04 andLast thress characters represent SubClass i.e. 182Now I want to fill thress dropdowns (i.e. Product, Class, SubClass) with the distinct value from the database, where Subclass will populate on Class selection and Class will populate on Product selection.So can anyone suggest me the SQL query to get it to work ? Hope you understand the situation. I am using SQL server 2005.Tue, 04 Aug 2009 12:22:58 GMTsteven-733381