This can be modified to suit i think...
-
--results on 1 row........................
-
DECLARE @a varchar(8000)
-
SELECT @a=COALESCE(@a, '') + col01 + space(1) FROM (SELECT distinct (col01) from Dataset_Rows) as a
-
SELECT rtrim(ltrim(@a)) AS [MyValues]
-
Hi James and eaocomp
The modification required to make this work for the poster centres around the fact that a concatenated list of values is required
for each of the Customer names given. The code snippet you highlighted for amendment is not CustName centric (
unless of course Dataset_Rows has some restrictive criteria based on the CustName) therefore it would return everything if you get me.
In short, for each customer the SQL is required to trundle down the products column as it were, and grab each unique mention of a product and fetch those values into 'one line' per customer separating out the products with a delimiter value which in this case is a required comma.
If the user creates a
'user defined function' to perform this functionality then this can be referenced in any views with the 'required' parameter being passed from the 'CustName' column value and the defined line of products return values then become visible in the view as a displayed column. We know that we can reference views in any stored procedures so we get the best of both worlds should they ever be required later.
This is the sample SQL that demonstrates the point of calling the UDF(User defined function) the udf function call being bolded
-
-
SELECT DISTINCT [Cust Name], dbo.UDF_CustOrder([Cust Name]) AS Cust_Order
-
FROM dbo.[Sample Data]
-
The user defined function below to make this work is based on the table name 'Sample Data' as given obviously amend that to suit (hate spaces in object names but there you go) as follows:
-
-
CREATE FUNCTION dbo.UDF_CustOrder
-
( @custname varchar (50) )
-
RETURNS varchar(8000)
-
AS
-
BEGIN
-
DECLARE @Custorder varchar(255), @delimiter char
-
SET @delimiter = ','
-
SELECT @custorder = COALESCE(@custorder + @delimiter, '') +space(1)+ Cust_Order FROM (SELECT DISTINCT [Cust Name],Cust_Order FROM [Sample Data] where [Cust Name]=@CustName) c
-
RETURN ( SELECT ltrim(rtrim(@CustOrder)) AS [CustOrder])
-
END
-
Hope this helps
Jim :)
2
