combining data from two columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

combining data from two columns

Author

Topic

cactushead
Starting Member

1 Post

Posted - 2007-02-08 : 10:22:50

Basically what I am doing is uploading inventory information from our point of sale/inventory management software into our website. I have a third party app that does most of the work for me but I need a view of our item table in our POS that this 3rd party software will use as the data source for what gets uploaded into the web. I have created the view with the following sql statement:
CREATE VIEW dbo.vwsfwebitemswine
AS
SELECT dbo.Item.*, dbo.Category.Name AS CategoryName, dbo.Department.Name AS DepartmentName
FROM dbo.Item INNER JOIN
dbo.Category ON dbo.Item.CategoryID = dbo.Category.ID INNER JOIN
dbo.Department ON dbo.Item.DepartmentID = dbo.Department.ID
WHERE (dbo.Item.DepartmentID = 7) AND (dbo.Item.WebItem = 1)

As you can see I am pulling from 3 tables. Item, Category and Department so that I can get the actual category and department names not just their numeric ID codes referenced in the standalone item table. The view data looks like this:

UPC ….. CategoryName DepartmentName
12345….. Merlot Wine
34556….. Chadonnay Wine
Etc…

What I need is an additional column created that combines the DepartmentName column values with the CategoryName column values and includes a ~ in between like this.

UPC ….. CategoryName DepartmentName Combined
12345….. Merlot Wine Wine~Merlot
34556….. Chardonnay Wine Wine~Chardonnay
Etc…

I’m not a SQL expert by any stretch. I’ve gotten this far using this forum but I’m stuck. Any help would be greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-08 : 10:28:18

[code]
CREATE VIEW dbo.vwsfwebitemswine
AS
SELECT dbo.Item.*, dbo.Category.Name AS CategoryName, dbo.Department.Name AS DepartmentName,
dbo.Department.Name + '~' + dbo.Category.Name as Combined
FROM dbo.Item
INNER JOIN dbo.Category ON dbo.Item.CategoryID = dbo.Category.ID
INNER JOIN dbo.Department ON dbo.Item.DepartmentID = dbo.Department.ID
WHERE (dbo.Item.DepartmentID = 7)
AND (dbo.Item.WebItem = 1)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources