A simple query

Question

Hi,

this query works fine

    select tblProductDetails.ProductId as Id,
    stuff((select ',' + CAST(PD2.SpeValue as varchar(100))
    from tblProductDetails PD2 where tblProductDetails.ProductId = PD2.ProductId for xml path('')),1,1,'') Name

    from tblProductDetails group by tblProductDetails.ProductId

Why this query not works

select PD.ProductId as Id,
    stuff((select ',' + CAST(PD2.SpeValue as varchar(100))
    from PD PD2 where PD.ProductId = PD2.ProductId for xml path('')),1,1,'') Name

    from (select ProductId,SpeValue from tblProductDetails) PD group by PD.ProductId

Answer 1

;with PD as (SELECT ProductId,SpeValue    FROM   tblProductDetails)
select PD.ProductId as Id, 
    stuff((select ',' + CAST(PD2.SpeValue as varchar(100)) 
    from PD PD2 where PD.ProductId = PD2.ProductId for xml path('')),1,1,'') Name 
    from   PD group 
	by PD.ProductId

Answer 2

If you want to re-use the derived table, you need to declare it as CTE before the final query.

However, I see no need to do so in your case, just use tblProductDetails directly (aliased as you want it).

E.g.

select PD.ProductId as Id, 
    stuff((select ',' + CAST(PD2.SpeValue as varchar(100)) 
    from tblProductDetails PD2
 where PD.ProductId = PD2.ProductId 
for xml path('')),1,1,'') AS [Name] 

    from tblProductDetails PD group by PD.ProductId

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles