Rows into a single column without a cursor
-
So I have a product table that contains products. I then have a product detail table that stores details as individual line items in a child table.
The client has requested a report that lists all products in one column with all product details in a coma separated list in a second column.
I.E.
Column 1:
Blender
Column 2:
White, 110 Volt, Dual Speed, Pulse Feature, Low Noise, 1 Year Warranty
Right now I am using a function with a cursor to go through each row in the detail table and build the result string. But I don't like the idea of using a cursor. Is there a way to do this with a set based query. I explored a pivot query but this isn't an aggregate query with a known number of columns and rows. Each product can of 0 to many rows in the detail table.
Suggestions??
-
Hey russ,
this is a pretty common question, with several different solutions. Why exactly do you need to concatenate all the rows on sql server? Typically this is something done on the application side depending on the scenario. Nevertheless,
DECLARE @stmts TABLE (id INT IDENTITY,
stmt NVARCHAR(MAX),
PRIMARY KEY (id)) -- your table
insert into @stmts (stmt)
select 'White' UNION
select '110 volt'
select stmt + ', ' from @stmts for xml path('') -- Using for xml path concatentates all the rows, and the + ', ' ensures there is no row name.
Result:
White, 110 volt,
Read up more where I got this from: http://www.sqlservercentral.com/Forums/Topic683335-338-1.aspx
---
Dlongnecker -
It always help if you provide sample code for people to work with here is a link to an article to help in the future: How to post data/code on a forum to get the best help
But since I just went through something similar, here is a solution that should work for you:
[font="Courier New"]CREATE TABLE #Product
(
ProductID INT NOT NULL,
ProductName VARCHAR(40) NOT NULL
);
--Add some sample data
INSERT INTO #Product
SELECT 1, 'Blender';
CREATE TABLE #ProductFeature
(
ProductID INT NOT NULL,
Feature VARCHAR(40) NOT NULL
);
--Add some sample data
INSERT INTO #ProductFeature
SELECT 1, 'White' UNION ALL
SELECT 1, '110 Volt' UNION ALL
SELECT 1, 'Dual Speed' UNION ALL
SELECT 1, 'Pulse Feature' UNION ALL
SELECT 1, 'Low Noise' UNION ALL
SELECT 1, '1 Year Warranty';
--Display the sample data
SELECT *
FROM #Product;
SELECT *
FROM #ProductFeature;
--Display the results
SELECT
t1.ProductID,
t1.ProductName,
STUFF((SELECT ', ' + CAST(t2.Feature AS VARCHAR(MAX))
FROM #ProductFeature t2
WHERE t2.ProductID = t1.ProductID
FOR XML PATH(''), TYPE).value('.[1]','varchar(max)')
,1,2,'') AS ProductFeatures --Use STUFF to remove the initial ", " from the returned list
FROM #Product t1;[/font]
I see Dlongnecker beat me to it, but I added the .VALUE portion to deal with characters in the features text that could cause a problem like >, <, and &. (Non printable characters, other than CR, LF, and TAB, will cause this to go boom, so hopefully that isn't an issue for you.)
edit: removed unnecessary group by.
-
Thanks I'll look into the xml path. The reason this isn't being handled at the application level is because they want this for a Crystal Report that can be ran outside of the application.
-
Very sneaky UMG Developer. That's a very hot solution. Right when I thought I knew a cool trick someone one-ups me.
ewwww... Crystal Reports. Fair Enough, Russ.
---
Dlongnecker -
Thanks again guys, the combination of responses gave me exactly what I needed. That stuff post that accounts for '&' and '' characters is pretty trick. I can guarantee that sooner or later the client will attempt to drop those characters into the details table.
The server is breathing a sigh of relief now that I am one step closer to getting rid of all cursors.
-
I'll add one more comment to this post for future forum users. I discovered that I had to have the database option "arithabort" set to 'ON' when using this method.
When I would encounter a product with no details in the child table Crystal would report an error. Crystal of course just said 'Error' but using visual studio's debugger I was able to discover the procedure was tossing a divide by zero error.
With "arithabort" set to ON, products with no child details simply come back as a null field, which is correct in this scenario.
-
russ.thomas (5/13/2009)
I'll add one more comment to this post for future forum users. I discovered that I had to have the database option "arithabort" set to 'ON' when using this method.When I would encounter a product with no details in the child table Crystal would report an error. Crystal of course just said 'Error' but using visual studio's debugger I was able to discover the procedure was tossing a divide by zero error.
With "arithabort" set to ON, products with no child details simply come back as a null field, which is correct in this scenario.
Interesting, I added a second product to my sample with no matching records in the ProductFeature table and it returned NULL as I expected, and I don't have "arithabort" turned on.
Can you post the query/view that you were seeing this issue on? (I'm thinking that it is something else in your query causing the divide by zero error.)
-
The interesting thing is that with it 'ON' or 'OFF' SQL behaves the same. It returns a null if no child records are found.
Crystal however was only happy with arithabort set 'ON'. I'm doing some more testing to see if I can get any more information but that was my experience.
I'm on SQL2008 but I doubt they changed the behavior since 2005.
-
I created a view and connected using Crystal Reports 10, and it didn't give me an errors. (I am using SQL Server 2005.)
I could try Crystal Reports XI if that would help.
I would still like to see the view you have a problem with.
-
I'm using Crystal 2008, SQL Server 2008 and Visual Studio 2008.
Here is the exact error message I get when I run the crystal report while debugging within VS. This is with arithabort set 'OFF'.
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
This is my UDF which get's called from the view below. Notice that if I rem out the XML Path block Crystal is happy, but If I include the XML Path block Crystal will only run the report based on the view that uses this UDF if arithabort is set to 'ON'
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ProductDetailString]
(
@pProductKey int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @pResult varchar(8000)
DECLARE @pDetail varchar(100)
SELECT @pResult = Ltrim(Rtrim(IsNull(Number,'')+': '+IsNull(Model,''))) FROM Product WHERE ProductKey = @pProductKey
/*** when this block is taken out crystal runs fine with or without arithabort
when this block is in, crystal will only run with arithabort set to ON **/
SELECT @pResult = @pResult + ' ' +
IsNull((STUFF((SELECT ', ' + CAST(Detail AS VARCHAR(MAX))
FROM Detail WHERE ProductKey = @pProductKey
FOR XML PATH(''), TYPE).value('.[1]','varchar(max)'),1,2,'')),'')
/*** xml path block ends here **/
IF SubString(@pResult,1,1) = ':'
BEGIN
SET @pResult = SubString(@pResult,2,Len(@pResult)-1)
END
RETURN Ltrim(Rtrim(@pResult))
END
This is the view that calls the UDF. Notice the view does call other UDF's but the error goes away if I take out the ProductDetailString UDF or modify the UDF as described above.
SELECT dbo.Product.ProductKey, dbo.Product.CategoryKey, dbo.Category.Title AS CategoryTitle, dbo.Product.Model,
dbo.ProductPriceString(dbo.Product.ProductKey) AS Price, dbo.Product.BidPrice, dbo.Product.Discount, dbo.Product.PriceDetail,
dbo.Product.ProductURL, dbo.Product.Description, dbo.Product.Sort, dbo.Catalog.CatalogKey, dbo.Catalog.Title AS CatalogTitle,
dbo.ProductDetailString(dbo.Product.ProductKey) AS Details, dbo.Category.Make, dbo.Product.LastUpdate, dbo.Product.Number,
dbo.PromoProduct(dbo.Product.ProductKey, GETDATE()) AS Promo, dbo.Product.PromoEnd, dbo.Product.PromoDetail, dbo.Contract.ContractNum,
dbo.Supplier.SupplierKey, dbo.Supplier.Supplier, dbo.Supplier.SupplierURL
FROM dbo.Product INNER JOIN
dbo.Category ON dbo.Product.CategoryKey = dbo.Category.CategoryKey INNER JOIN
dbo.Catalog ON dbo.Category.CatalogKey = dbo.Catalog.CatalogKey LEFT OUTER JOIN
dbo.Contract ON dbo.Category.ContractKey = dbo.Contract.ContractKey LEFT OUTER JOIN
dbo.Supplier ON dbo.Contract.SupplierKey = dbo.Supplier.SupplierKey
WHERE (dbo.Product.Status = 'Active')
-
russ.thomas (5/13/2009)
I'm using Crystal 2008, SQL Server 2008 and Visual Studio 2008.Here is the exact error message I get when I run the crystal report while debugging within VS. This is with arithabort set 'OFF'.
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
The image is too small to read anything, can you post a larger version
-
I thought the link I provided took you to a larger version. Apparantly not, sorry. I've attached the image here:
-
Hmm, no clue from the error message then
but if its the XML PATH part thats causing the problem you could replace it with the old simple SQL 2000 way of doing things - i.e.
DECLARE @pResult varchar(100);
SET @pResult = [.. snip ..];
SELECT @pResult = @pResult + IsNull(Detail,'') + ', '
FROM Detail
WHERE ProductKey = @pProductKey
-- ORDER BY ... if required
RETURN @pResult
Such functions worked fine for beating the socks off cursors before XML PATH appeared.
http://www.sqlservercentral.com/articles/Advanced+Querying/replacingcursorsandwhileloops/1956/
-
Though the technical trickery of the XML path is cool, I am really disappointed in myself for not coming up with the simplicity of the "select =" statement. Thanks everyone for the input. The issue has been solved for my solution everything else has been extremely educational.
In parting...
I'm more and more convinced that the error from above is purely a Crystal problem. Calling those functions from within query analyzer cause no problems with or without arithabort, it's only when calling it from within crystal that I get an error. I think they are pre-emptively requiring the setting whether it actually needs it or not. Especially since the error it's self says, incorrect setting.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply