Concatenate fields.

I tried to use this example an in my case I am getting error
Ambiguous column name on those two lines for ProductName.

CASE WHEN length = 0 THEN '' ELSE ', ' END + ProductName AS VARCHAR(8000) ),
CAST( ProductName AS VARCHAR(8000)), length + 1



Here is complete code:
;WITH CTE ( CategoryId, product_list, product_name, length )
AS ( SELECT CategoryId, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM Northwind..Products
GROUP BY CategoryId
UNION ALL
SELECT p.CategoryId, CAST( product_list +
CASE WHEN length = 0 THEN '' ELSE ', ' END + ProductName AS VARCHAR(8000) ),
CAST( ProductName AS VARCHAR(8000)), length + 1
FROM CTE c
INNER JOIN Northwind..Products p
ON c.CategoryId = p.CategoryId
WHERE p.ProductName > c.product_name )
SELECT CategoryId, product_list
FROM ( SELECT CategoryId, product_list,
RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC )
FROM CTE ) D ( CategoryId, product_list, rank )
WHERE rank = 1 ;
TarasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry MillerCommented:
You need to determine which table the ProductName value is coming from (CTE c or Northwind..Products p).

For example,

SELECT p.CategoryId, CAST( product_list +
CASE WHEN length = 0 THEN '' ELSE ', ' END + c.ProductName AS VARCHAR(8000) ),
CAST( c.ProductName AS VARCHAR(8000)), length + 1
0
PortletPaulfreelancerCommented:
There are other syntax errors in the provided query
;WITH
        CTE (CategoryId, product_list, product_name, length)
        AS (
                        SELECT
                                CategoryId
                              , CAST('' AS varchar(8000))
                              , CAST('' AS varchar(8000))
                              , 0
                        FROM Northwind..Products
                        GROUP BY CategoryId
                        UNION ALL
                                SELECT
                                        p.CategoryId
                                      , CAST(p.product_list +  --<< p or c?
                                                CASE
                                                WHEN length = 0 THEN ''
                                                ELSE ', '
                                                END + p.ProductName AS varchar(8000)) --<< p or c?
                                      , CAST(p.ProductName AS varchar(8000)) --<< p or c?
                                      , length + 1
                                FROM CTE c
                                INNER JOIN Northwind..Products p
                                        ON c.CategoryId = p.CategoryId
                                WHERE p.ProductName > c.product_name
                )
SELECT
        CategoryId
      , product_list
FROM (
        SELECT
                CategoryId
              , product_list
              , RANK() OVER (PARTITION BY CategoryId ORDER BY length DESC) AS my_rank
        FROM CTE
) D
--(CategoryId, product_list, rank)
WHERE my_rank = 1

Open in new window

lines 14-19, see comments above (p or c is needed)
line 33, needs a column alias (don't use "rank" as an alias though)
line 36, that list of fields in parentheses isn't needed
line 37, use the alias used on  line 33

Additionally are you certain you want to use the function RANK()?
Its likely you want to use ROW_NUMBER() instead

What is it that you wish to achieve by the query?

{+edit, correction to line number references}
0
TarasAuthor Commented:
I was asking this question under SQL Server I don’t know why it is going to crystal reports
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

TarasAuthor Commented:
PortletPaul.
One categoryID could have several different Product_Names.
I want to concatenate them in one filed - Product list.
0
PortletPaulfreelancerCommented:
Try this please
SELECT
      CategoryId
    , max(plist) AS product_list
FROM Northwind..Products
CROSS APPLY (
        SELECT STUFF((SELECT ','+ p.ProductName
                  FROM Northwind..Products AS p
                  WHERE Northwind..Products.CategoryId = p.CategoryId
                  ORDER BY p.ProductName
                  FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)')
              ,1,1,'')
            ) AS ca1 (plist)
GROUP BY
      CategoryId
;

Open in new window

0
PortletPaulfreelancerCommented:
sorry, this would be better then the above for performance:
SELECT
      CategoryId
    , product_list
FROM (
      SELECT
            CategoryId
      FROM Northwind..Products
      GROUP BY
            CategoryId
      ) AS p1
CROSS apply (
        SELECT STUFF((SELECT ','+ p2.ProductName
                  FROM Northwind..Products AS p2
                  WHERE p1.CategoryId = p2.CategoryId
                  ORDER BY p2.ProductName
                  FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)')
              ,1,1,'')
            ) AS ca1 (product_list)
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.