Solved

SQL Query to append data to a field

Posted on 2016-07-14
9
69 Views
1 Endorsement
Last Modified: 2016-07-18
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
Comment
Question by:Member_2_7967487
  • 5
  • 4
9 Comments
 
LVL 7

Expert Comment

by:Dung Dinh
ID: 41711876
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
 

Author Comment

by:Member_2_7967487
ID: 41712976
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
 
LVL 7

Expert Comment

by:Dung Dinh
ID: 41712997
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
 

Author Comment

by:Member_2_7967487
ID: 41713241
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Member_2_7967487
ID: 41714501
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
 
LVL 7

Expert Comment

by:Dung Dinh
ID: 41714625
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
 

Author Comment

by:Member_2_7967487
ID: 41714659
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
 
LVL 7

Accepted Solution

by:
Dung Dinh earned 500 total points
ID: 41716233
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
 

Author Comment

by:Member_2_7967487
ID: 41718118
Thank you Dung,

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MSSQL 2014 Query Synthax 8 38
Retention Policy for Backups 1 13
Instering to MySQL table 5 16
Insert data into database 2 0
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now