Simple the Query Duplication in SQL Server

Question

I need to filter the query based on the below requirement..

My query :

select * from tbltemp

Current output :

Caterogy   SeqCategory   DescofChange    RequestId     TaskCompVer
-----------------------------------------------------------------------------    
BIGBEAR    BIGBEAR       BIGBEAR         B14020002     Provide ASPM Wish List
ARCUS3PL   KOJN-RE       ARCUS3PL        B14020002     Provide ASPM Wish List
AURORA     Aurora        Aurora          B14020003     Provide ASPM Wish List

Desired output:

Caterogy           SeqCategory       DescofChange      RequestId   TaskCompVer
---------------------------------------------------------------------------------------    
BIGBEAR,ARCUS3PL   BIGBEAR,KOJN-RE   BIGBEAR,ARCUS3PL  B14020002   Provide ASPM Wish List
AURORA             Aurora            Aurora            B14020003   Provide ASPM Wish List

How I need to filter the above select query in such way it generates the Actual output..

I tried using STUFF but it's throwing syntax error :

SELECT
    RequestId, 
    STUFF((SELECT ', ' + temp2.WishItemPE
           FROM tbltemp temp2  
           WHERE temp2.TaskCompVer = temp1.TaskCompVer 
             AND temp2.RequestId = temp1.RequestId        
           FOR XML PATH('')), 1, 1, '') AS WishItemPE  
FROM
    tbltemp

Error:

Incorrect syntax near 'XML'

see http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Ian Kenney, Feb 19 '14 at 08:39

Milen · Answer 1 · 2014-02-20T08:47:54.163

You can use the stuff function:

select distinct stuff(
  ( select  cast(',' as varchar(max)) + t1.Category
    from temp t1
    WHERE t1.RequestID = t.RequestID
    order by t1.Category
    for xml path('')
    ), 1, 1, '') as Category,

 stuff(
  ( select  cast(',' as varchar(max)) + t1.SeqCategory
    from temp t1
    WHERE t1.RequestID = t.RequestID
    order by t1.SeqCategory
    for xml path('')
    ), 1, 1, '') as SeqCategory,
 stuff(
  ( select  cast(',' as varchar(max)) + t1.DescofChange
    from temp t1
    WHERE t1.RequestID = t.RequestID
    order by t1.DescofChange
    for xml path('')
    ), 1, 1, '') as DescofChange, 
  RequestID, 
 stuff(
  ( select  Distinct cast(',' as varchar(max)) + t1.TaskProvider
    from temp t1
    WHERE t1.RequestID = t.RequestID

    for xml path('')
    ), 1, 1, '') as TaskProvider
from temp t

demo here: http://sqlfiddle.com/#!3/f1789/9

EDIT Alternative to this can be CROSS APPLY:

SELECT  Categories, SeqCategories, DescofChanges, RequestID, TaskProvider
FROM temp as A
Cross Apply
(
 SELECT Category + ',' 
 FROM temp AS B 
 WHERE A.RequestID = B.RequestID FOR XML PATH('')
)D (Categories)
Cross Apply
(
 SELECT SeqCategory + ',' 
 FROM temp AS B 
 WHERE A.RequestID = B.RequestID FOR XML PATH('')
)E (SeqCategories)
Cross Apply
(
 SELECT DescofChange + ',' 
 FROM temp AS B 
 WHERE A.RequestID = B.RequestID FOR XML PATH('')
)F (DescofChanges)

GROUP BY RequestID,  Categories, SeqCategories, DescofChanges, TaskProvider

demo: http://sqlfiddle.com/#!3/f1789/56

sry..i am using old sqlserver database...that's y? STUFF is not working..thanks — Kapil, Feb 19 '14 at 09:35
your question was tagged with `SQL SERVER 2008` and `STUFF` is in this version... — Milen, Feb 19 '14 at 09:40
I am using SQL 2008 But I am connecting to older one that's y? Stuff is not working .. — Kapil, Feb 20 '14 at 03:08

score 0 · Answer 2 · answered Feb 19 '14 at 09:00

Firstly, why was the error,

1.You didn't specify the table alias for base table. Last line in you Query.

FROM 
 tbltemp temp1
         ^here

2.There is no column in table tbltemp with column name WishItemPE. Line no. 3.

Finally, you have to use below Query to get the desired output.

STUFF((SELECT ', ' + temp2.WishItemPE

Final Query

SELECT distinct
    STUFF((SELECT ', ' + temp2.[Caterogy]
           FROM tbltemp temp2  
           WHERE temp2.TaskCompVer = temp1.TaskCompVer 
             AND temp2.RequestId = temp1.RequestId     
           FOR XML PATH('')), 1, 1, '') AS [Caterogy],
    RequestId, 
    STUFF((SELECT ', ' + temp2.[SeqCategory]
           FROM tbltemp temp2  
           WHERE temp2.TaskCompVer = temp1.TaskCompVer 
             AND temp2.RequestId = temp1.RequestId     
           FOR XML PATH('')), 1, 1, '') AS [SeqCategory],
    STUFF((SELECT ', ' + temp2.[DescofChange]
           FROM tbltemp temp2  
           WHERE temp2.TaskCompVer = temp1.TaskCompVer 
             AND temp2.RequestId = temp1.RequestId     
           FOR XML PATH('')), 1, 1, '') AS [DescofChange],
    [TaskCompVer]
FROM
    tbltemp as temp1

SQL Fiddle

Simple the Query Duplication in SQL Server

2 Answers2