Link to home
Start Free TrialLog in
Avatar of Member_2_7967487
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

User generated image
Avatar of Dung Dinh
Dung Dinh
Flag of Viet Nam image

There are many ways that can help you concatenate string. You can use CROSS APPLY or use FOR XML with PATH mode in SQL Server. Try with below:
DECLARE @CategoryIndex TABLE (ID int, CategoryName nvarchar(50))
INSERT INTO @CategoryIndex
VALUES (1, 'Furniture'),
(2, 'Tables'),
(3, 'Chairs'),
(4, 'Classic'),
(5, 'Designs'),
(6, 'Theme')

DECLARE @Category TABLE
(
ID int,
[Category 1] nvarchar(50),
[Category 2] nvarchar(50),
[Category 3] nvarchar(50),
[Category 4] nvarchar(50),
[Category 5] nvarchar(50),
[Category 6] nvarchar(50),
[Category 7] nvarchar(50),
[Category 8] nvarchar(50),
[Category 9] nvarchar(50)
)
INSERT INTO @Category
VALUES (1,'Chairs','Tables','Theme','Furniture',NULL,'Theme','Classic','Furniture','Chairs'),
(2,'Tables','Theme','Furniture','Theme','Classic',NULL,NULL,'Theme','Tables')


SELECT cat1.ID,[Category 1] ,[Category 2] ,[Category 3] ,[Category 4] ,[Category 5] ,[Category 6] 
,[Category 7] ,[Category 8] ,[Category 9] ,
               STUFF(
                    (SELECT ','+CONVERT(nvarchar(50),CategoryID)
                    FROM         (
                                SELECT t1.ID,i1.ID AS CategoryID
                                FROM @Category t1
                                      INNER JOIN @CategoryIndex i1 ON t1.[Category 1] = i1.CategoryName
                                UNION ALL
                                SELECT t1.ID,i1.ID AS CategoryID
                                FROM @Category t1
                                      INNER JOIN @CategoryIndex i1 ON t1.[Category 2] = i1.CategoryName
                                UNION ALL
                                SELECT t1.ID,i1.ID AS CategoryID
                                FROM @Category t1
                                      INNER JOIN @CategoryIndex i1 ON t1.[Category 3] = i1.CategoryName                    
                                UNION ALL
                                SELECT t1.ID,i1.ID AS CategoryID
                                FROM @Category t1
                                      INNER JOIN @CategoryIndex i1 ON t1.[Category 4] = i1.CategoryName                    
                                UNION ALL
                                SELECT t1.ID,i1.ID AS CategoryID
                                FROM @Category t1
                                      INNER JOIN @CategoryIndex i1 ON t1.[Category 5] = i1.CategoryName                    
                                UNION ALL
                                SELECT t1.ID,i1.ID AS CategoryID
                                FROM @Category t1
                                      INNER JOIN @CategoryIndex i1 ON t1.[Category 6] = i1.CategoryName                    
                                UNION ALL
                                SELECT t1.ID,i1.ID AS CategoryID
                                FROM @Category t1
                                      INNER JOIN @CategoryIndex i1 ON t1.[Category 7] = i1.CategoryName                    
                                UNION ALL
                                SELECT t1.ID,i1.ID AS CategoryID
                                FROM @Category t1
                                      INNER JOIN @CategoryIndex i1 ON t1.[Category 8] = i1.CategoryName                    
                                UNION ALL
                                SELECT t1.ID,i1.ID AS CategoryID
                                FROM @Category t1
                                      INNER JOIN @CategoryIndex i1 ON t1.[Category 9] = i1.CategoryName
                             ) cat2
                             WHERE cat1.ID = cat2.ID
                             FOR XML PATH('')),1,1,'') AS CategoryIDs               
FROM @Category cat1
    

Open in new window

User generated image
Avatar of Member_2_7967487
Member_2_7967487

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
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.
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','Theme','Furniture',NULL,'Theme','Classic','Furniture','Chairs'),
(2,'Tables','Theme','Furniture','Theme','Classic',NULL,NULL,'Theme','Tables')


User generated image
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'


 User generated image
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.

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

Open in new window

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


User generated image
 
User generated image
ASKER CERTIFIED SOLUTION
Avatar of Dung Dinh
Dung Dinh
Flag of Viet Nam image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you Dung,

This did it!!! YEAH!!!! :-D