Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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

SQL.jpg
1
Member_2_7967487
Asked:
Member_2_7967487
  • 5
  • 4
1 Solution
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
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

FOR XML PATH to concatenate string
0
 
Member_2_7967487Author Commented:
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
0
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Member_2_7967487Author Commented:
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')


SQL2.jpg
0
 
Member_2_7967487Author Commented:
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'


 SQL3.jpg
0
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
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

0
 
Member_2_7967487Author Commented:
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


SQL4.jpg
 
SQL5.jpg
0
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
I corrected as your expectation
DECLARE @CategoryIndex TABLE (ID int, CategoryName nvarchar(50))
INSERT INTO @CategoryIndex
VALUES (1, 'Furniture'),
(2, 'Tables'),
(3, 'Chairs'),
(4, 'Classic'),
(5, 'Designs'),
(6, 'Theme')

-- Create Item table
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),
[CategoryIDs] nvarchar(50)
)

--- Insert data
INSERT INTO @Category
VALUES (1,'Chairs','Tables','Theme','Furniture',NULL,'Theme','Classic','Furniture','Chairs',NULL),
(2,'Tables','Theme','Furniture','Theme','Classic',NULL,NULL,'Theme','Tables',NULL),
(3,'Designs','Classic','Tables',NULL,'Furniture','Tables',NULL,NULL,'Designs',NULL),
(4,'Furniture',NULL,'Furniture',NULL,NULL,NULL,'Furniture','Theme','Furniture',NULL),
(5,'Designs','Chairs','Theme','Classic','Classic','Designs',NULL,NULL,NULL,NULL),
(6,'Furniture','Furniture','Tables',NULL,NULL,NULL,'Designs','Theme','Tables',NULL)


--- Start updating CategoryID
--- Replace all '|' to any delimited character you want

UPDATE item
SET CategoryIDs =   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 @Category 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

SELECT * FROM @CategoryIndex
SELECT * FROM @Category

Open in new window


Hopefully, It can help you :)
Final_Result.PNG
0
 
Member_2_7967487Author Commented:
Thank you Dung,

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now