Member_2_7967487
asked on
SQL Query to append data to a field
Hi,
I would like some help on how to create a SQL query that would look at 'Category 1-9' columns and append the number according to the index listed below to the CategoryIds column separated by a comma. If the column is NULL, then skip over to the next column. The final result highlighted in yellow as an example.
I would like to run this query on every row from 'Category 1-9' columns and append the results to CategoryIds column . BIG THANKS!!!
INDEX:
1 Furniture
2 Tables
3 Chairs
4 Classic
5 Designs
6 Theme
I would like some help on how to create a SQL query that would look at 'Category 1-9' columns and append the number according to the index listed below to the CategoryIds column separated by a comma. If the column is NULL, then skip over to the next column. The final result highlighted in yellow as an example.
I would like to run this query on every row from 'Category 1-9' columns and append the results to CategoryIds column . BIG THANKS!!!
INDEX:
1 Furniture
2 Tables
3 Chairs
4 Classic
5 Designs
6 Theme
ASKER
This is EXCELLENT!!!! Thank you So MUCH, Dung!! :-D
I am not a SQL expert. Where do we change if we want to separate by a symbol | instead of a command in the CategoryIDs?
Example:
CateogryIDs
3|2|4|3
Also, now that I know this works, how do I ran the query to apply to the entire db to update the CategoryIDs? I will add additional items number index to the VALUES and want to make sure this would be the only place to update.
VALUES (1, 'Furniture'),
(2, 'Tables'),
(3, 'Chairs'),
(4, 'Classic'),
(5, 'Designs'),
(6, 'Theme')
Thanks again for your help!
Khoa
I am not a SQL expert. Where do we change if we want to separate by a symbol | instead of a command in the CategoryIDs?
Example:
CateogryIDs
3|2|4|3
Also, now that I know this works, how do I ran the query to apply to the entire db to update the CategoryIDs? I will add additional items number index to the VALUES and want to make sure this would be the only place to update.
VALUES (1, 'Furniture'),
(2, 'Tables'),
(3, 'Chairs'),
(4, 'Classic'),
(5, 'Designs'),
(6, 'Theme')
Thanks again for your help!
Khoa
Go to the STUFF line code and repalce SELECT ',' to SELCT '|'
Yes, you could add more addtional items to @CategoryIndex.
In my example, I used the table variable. But you can create base tables to store your items permanently.
Yes, you could add more addtional items to @CategoryIndex.
In my example, I used the table variable. But you can create base tables to store your items permanently.
ASKER
This works great!
From the screenshot, I am trying to see where in the Query to apply to the highlighted columns, rather only the first two items without having to input the values in the query. If it possible?
INSERT INTO @Category
VALUES (1,'Chairs','Tables','Them e','Furnit ure',NULL, 'Theme','C lassic','F urniture', 'Chairs'),
(2,'Tables','Theme','Furni ture','The me','Class ic',NULL,N ULL,'Theme ','Tables' )
From the screenshot, I am trying to see where in the Query to apply to the highlighted columns, rather only the first two items without having to input the values in the query. If it possible?
INSERT INTO @Category
VALUES (1,'Chairs','Tables','Them
(2,'Tables','Theme','Furni
ASKER
Does anyone knows the shorter ways to append the CategoryIds field? Below only update the CateoryIds field.
UPDATE [Inventory].[dbo].[Items]
SET [CategoryIds] = '2'
FROM [Inventory].[dbo].[Items]
where [Category 1] = 'Chairs'
UPDATE [Inventory].[dbo].[Items]
SET [CategoryIds] = '3'
FROM [Inventory].[dbo].[Items]
where [Category 1] = 'Tables'
UPDATE [Inventory].[dbo].[Items]
SET [CategoryIds] = '2'
FROM [Inventory].[dbo].[Items]
where [Category 1] = 'Chairs'
UPDATE [Inventory].[dbo].[Items]
SET [CategoryIds] = '3'
FROM [Inventory].[dbo].[Items]
where [Category 1] = 'Tables'
Sorry if I could not understand your expectation clearly. As your example, I am assuming that you have a table [Inventory].[dbo].[Items] to store Items data and another table that named as CategoryIndex for storing CategoryID such as:
(1, 'Furniture'),
(2, 'Tables'),
(3, 'Chairs'),
(4, 'Classic'),
(5, 'Designs'),
(6, 'Theme')
.....
So now you need to update CategoryIDs column in [Inventory].[dbo].[Items] based on value of the columns [Category 1],... [Category 9]. Back to my example, you can adjust it to support update what you want.
(1, 'Furniture'),
(2, 'Tables'),
(3, 'Chairs'),
(4, 'Classic'),
(5, 'Designs'),
(6, 'Theme')
.....
So now you need to update CategoryIDs column in [Inventory].[dbo].[Items] based on value of the columns [Category 1],... [Category 9]. Back to my example, you can adjust it to support update what you want.
UPDATE item
SET CategoryIDs = REPLACE(STUFF((','+ ISNULL(CONVERT(nvarchar(50),i.ID),'')
+','+ISNULL(CONVERT(nvarchar(50),i2.ID),'')
+','+ISNULL(CONVERT(nvarchar(50),i3.ID),'')
+','+ISNULL(CONVERT(nvarchar(50),i4.ID),'')
+','+ISNULL(CONVERT(nvarchar(50),i5.ID),'')
+','+ISNULL(CONVERT(nvarchar(50),i6.ID),'')
+','+ISNULL(CONVERT(nvarchar(50),i7.ID),'')
+','+ISNULL(CONVERT(nvarchar(50),i8.ID),'')
+','+ISNULL(CONVERT(nvarchar(50),i9.ID),'')
) ,1,1,''),',,',',')
FROM [Inventory].[dbo].[Items] item
LEFT JOIN CategoryIndex i ON item.[Category 1] = i.CategoryName
LEFT JOIN CategoryIndex i2 ON item.[Category 2] = i2.CategoryName
LEFT JOIN CategoryIndex i3 ON item.[Category 3] = i3.CategoryName
LEFT JOIN CategoryIndex i4 ON item.[Category 4] = i4.CategoryName
LEFT JOIN CategoryIndex i5 ON item.[Category 5] = i5.CategoryName
LEFT JOIN CategoryIndex i6 ON item.[Category 6] = i6.CategoryName
LEFT JOIN CategoryIndex i7 ON item.[Category 7] = i7.CategoryName
LEFT JOIN CategoryIndex i8 ON item.[Category 8] = i8.CategoryName
LEFT JOIN CategoryIndex i9 ON item.[Category 9] = i9.CategoryName
ASKER
Hi Dung,
Thank you for your help!! This addition works out much better.
I have two items not showing up properly.
1) I followed your instructions below, but the output is in commas per screenshot, rather |
----- Go to the STUFF line code and replace SELECT ',' to SELECT '|'
2) Is there any way to skip over any column that has NULL or empty and not output in the CategoryIds column? It is showing up as a space in the Categoryids (highlighted in yellow). The final output would look like the below.
(Correct)
CateogryIDs
3|2|4|3
(Not Correct)
CateogryIDs
3|2|4||3||4
Thank you for your help!! This addition works out much better.
I have two items not showing up properly.
1) I followed your instructions below, but the output is in commas per screenshot, rather |
----- Go to the STUFF line code and replace SELECT ',' to SELECT '|'
2) Is there any way to skip over any column that has NULL or empty and not output in the CategoryIds column? It is showing up as a space in the Categoryids (highlighted in yellow). The final output would look like the below.
(Correct)
CateogryIDs
3|2|4|3
(Not Correct)
CateogryIDs
3|2|4||3||4
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Dung,
This did it!!! YEAH!!!! :-D
This did it!!! YEAH!!!! :-D
Open in new window