tblCategory
tblBrands
And we want the result display as:
Now we have to find out the best way to achieve this requirement.
The solutions which we are going to explore will use two SQL commands STUFF and FOR XML. We will explain about these commands later in this tutorial.
As all of us will be aware about INNER JOIN so first lets write it in very simple manner:-
SELECT c.cat_name
,b.brand_nme
FROM tblCategory c
JOIN tblBrands b ON b.cat_id = c.id
ORDER BY 1, 2
And we will get the results in following way
Lets take one step ahead and use FOR XML PATH option which will return the result as XML string and will put all the data into one row and column.
SELECT c.cat_name
,b.brand_nme
FROM tblCategory c
JOIN tblBrands b ON b.cat_id = c.id
ORDER BY 1, 2
FOR XML PATH ('')
And here the result will appear as
Now try to convert the join into part of the select statement
SELECT c.cat_name
,(SELECT '; '+b.brand_nme FROM tblBrands b WHERE b.cat_id = c.id FOR XML PATH('')) [Section]
FROM tblCategory c
ORDER BY 1
The result would be like
Now finally use the STUFF command to fulfill our requirement
SELECT c.cat_name
,STUFF((SELECT '; '+b.brand_nme FROM tblBrands b WHERE b.cat_id = c.id FOR XML PATH('')), 1, 1, '') [Section]
FROM tblCategory c
GROUP BY c.cat_name, c.id
ORDER BY 1
And here we go!
There might be other best options to achieve this requirement but this one I used during my project. Other best solutions and comments are also most welcome!
Hello,
ReplyDeleteMy name is Amos. I would like to say that your tip is very helpful.
I would like propose an additional way to perform the required query, maybe you've thought about that.
There is a possibility to write a scalar function that gets as a parameter the category id and returns a
a string of the brands' names of the category. And afterthat , in your query, you have only to activate the function.
Hereby the function: I assume that the category id is of type int
-- A function that returns a string that contains the
-- brands' names for a certain id given group
-- separated by ;
create function [dbo].[getBrands]
(
@id int
)
returns varchar(500)
as
begin
declare @brandsStr varchar(500);
-- Initializing with '' the items string for the current id
set @brandsStr = '';
-- Putting all the names of brands to the current id
-- separated by ; in the varriable @brandsStr
select @brandsStr = @brandsStr + brand_nme + ';'
from tblBrands
where cat_id = @id;
-- Returning the string that contains the brands' names
-- The last ; is omitted by using the function stuff
return stuff(@brandsStr, len(@brandsStr), 1, '');
end
Now the query becomes simpler
select cat_name,
–- Activating the function
dbo. getBrands (id) as brands
from tblCategory c
group by c.cat_name, c.id
order by 1
If you've any comments, you can send an e-mail to me:
amos_s12@zahav.net.il
Thank you,
Amos