none
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

    Thursday, March 27, 2014 6:49 PM

Answers

  • ;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
    



    Thursday, March 27, 2014 6:54 PM
    Moderator

All replies

  • ;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
    



    Thursday, March 27, 2014 6:54 PM
    Moderator
  • 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

    Thursday, March 27, 2014 6:58 PM
    Moderator