Reading Columns
-
Greetings All, I have a particular problem that I am hoping someone with some TSQL know-how can assist me with. Here's the issue: I have a table that contains 40 some-odd columns. the first 2 columns are essentially identifiers. The following columns are components of the identifier. To give a clear picture, lets' say the first row has CHCK in the 1st column and Chocolate Cake in the 2nd column. The rest of the columns contains the ingredients of the Chocolate Cake (let's say 7 ingredients). The next row is for Grilled Cheese. This row would only have 3 ingredients. Here is what I would like to achieve: I want to run through each row and read every column to see if there is a value in it. If there is a value, I would like to put the identifier and the ingredient in another table with an insert so that this new table (with only 2 columns) looks something like this CHCK eggs CHCK milk GCHEES bread GCHEESE cheese Is there a way that I could do this in a stored procedure? I know that I could use a cursor to the rows but each row could have a variable number of *ingredients* and that is where I am lost. Thank you in advance for any suggestions you may offer.
-
Greetings All, I have a particular problem that I am hoping someone with some TSQL know-how can assist me with. Here's the issue: I have a table that contains 40 some-odd columns. the first 2 columns are essentially identifiers. The following columns are components of the identifier. To give a clear picture, lets' say the first row has CHCK in the 1st column and Chocolate Cake in the 2nd column. The rest of the columns contains the ingredients of the Chocolate Cake (let's say 7 ingredients). The next row is for Grilled Cheese. This row would only have 3 ingredients. Here is what I would like to achieve: I want to run through each row and read every column to see if there is a value in it. If there is a value, I would like to put the identifier and the ingredient in another table with an insert so that this new table (with only 2 columns) looks something like this CHCK eggs CHCK milk GCHEES bread GCHEESE cheese Is there a way that I could do this in a stored procedure? I know that I could use a cursor to the rows but each row could have a variable number of *ingredients* and that is where I am lost. Thank you in advance for any suggestions you may offer.
-
Greetings All, I have a particular problem that I am hoping someone with some TSQL know-how can assist me with. Here's the issue: I have a table that contains 40 some-odd columns. the first 2 columns are essentially identifiers. The following columns are components of the identifier. To give a clear picture, lets' say the first row has CHCK in the 1st column and Chocolate Cake in the 2nd column. The rest of the columns contains the ingredients of the Chocolate Cake (let's say 7 ingredients). The next row is for Grilled Cheese. This row would only have 3 ingredients. Here is what I would like to achieve: I want to run through each row and read every column to see if there is a value in it. If there is a value, I would like to put the identifier and the ingredient in another table with an insert so that this new table (with only 2 columns) looks something like this CHCK eggs CHCK milk GCHEES bread GCHEESE cheese Is there a way that I could do this in a stored procedure? I know that I could use a cursor to the rows but each row could have a variable number of *ingredients* and that is where I am lost. Thank you in advance for any suggestions you may offer.
STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like: Recipie Ingredients Where ingredients has a 1 to many relationship with the recipie field. If you are just starting then do some reading on data structures, database design before you continue. This is a typical clusterf**k created by someone who has no idea what they are doing. Do some research and get it right, otherwise you will waste a lot of your time attempting to undo these stupid structures.
Never underestimate the power of human stupidity RAH
-
Greetings All, I have a particular problem that I am hoping someone with some TSQL know-how can assist me with. Here's the issue: I have a table that contains 40 some-odd columns. the first 2 columns are essentially identifiers. The following columns are components of the identifier. To give a clear picture, lets' say the first row has CHCK in the 1st column and Chocolate Cake in the 2nd column. The rest of the columns contains the ingredients of the Chocolate Cake (let's say 7 ingredients). The next row is for Grilled Cheese. This row would only have 3 ingredients. Here is what I would like to achieve: I want to run through each row and read every column to see if there is a value in it. If there is a value, I would like to put the identifier and the ingredient in another table with an insert so that this new table (with only 2 columns) looks something like this CHCK eggs CHCK milk GCHEES bread GCHEESE cheese Is there a way that I could do this in a stored procedure? I know that I could use a cursor to the rows but each row could have a variable number of *ingredients* and that is where I am lost. Thank you in advance for any suggestions you may offer.
select column1,convert(varchar(20),isnull(column2,''))+''+convert(varchar(20),isnull(column3'')) as coluumn2 from tablename use the above query and write like above for all the ingredent column . lets me know if any things is needed.
-
select column1,convert(varchar(20),isnull(column2,''))+''+convert(varchar(20),isnull(column3'')) as coluumn2 from tablename use the above query and write like above for all the ingredent column . lets me know if any things is needed.
sorry for the above reply use this select coumun1,column2 from table1 where column2 is not null union all select coumun1,column3 from table1 where column3 is not null union all select coumun1,column4 from table1 where column4 is not null here for three column u have to write for all column
-
Greetings All, I have a particular problem that I am hoping someone with some TSQL know-how can assist me with. Here's the issue: I have a table that contains 40 some-odd columns. the first 2 columns are essentially identifiers. The following columns are components of the identifier. To give a clear picture, lets' say the first row has CHCK in the 1st column and Chocolate Cake in the 2nd column. The rest of the columns contains the ingredients of the Chocolate Cake (let's say 7 ingredients). The next row is for Grilled Cheese. This row would only have 3 ingredients. Here is what I would like to achieve: I want to run through each row and read every column to see if there is a value in it. If there is a value, I would like to put the identifier and the ingredient in another table with an insert so that this new table (with only 2 columns) looks something like this CHCK eggs CHCK milk GCHEES bread GCHEESE cheese Is there a way that I could do this in a stored procedure? I know that I could use a cursor to the rows but each row could have a variable number of *ingredients* and that is where I am lost. Thank you in advance for any suggestions you may offer.
for sqlserver 2005 use unpivot method
-
STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like: Recipie Ingredients Where ingredients has a 1 to many relationship with the recipie field. If you are just starting then do some reading on data structures, database design before you continue. This is a typical clusterf**k created by someone who has no idea what they are doing. Do some research and get it right, otherwise you will waste a lot of your time attempting to undo these stupid structures.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like: Recipie Ingredients
I think that is what he wants to do.
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
-
for sqlserver 2005 use unpivot method
Whoa, Whoa guys. I may not have explained myself clearly from the responses I have received. The database tables are in the correct format as you have suggested. I need a query to pull the data in the format I mentioned to another party. I am not certain how they will be utilizing the data in this manner but that what has been spec'd out to me. Thank you Anup for your assistance and I will further explore the command you have suggested.