CODEWITHSUNDEEP
sqlsql-serversql-server-2008
satyajit
satyajit

Reputation: 2700

Implementing CTE

I have the following table:

create table #prd
(
  ver varchar(20),
  fam varchar(20),
  mm varchar(20),
)

insert into #prd values('dt','fam1','111')
insert into #prd values('dt','fam1','222')
insert into #prd values('mb','fam1','333')
insert into #prd values('mb','fam2','444')

I need the output as below using CTE:

ver       family       MM
-------   ---------    ----------    
DT          fam1         111,222  
mb          fam1         333  
mb          fam2         444

Please help!!

Upvotes: 3

Views: 426

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

A CTE version that does not use for xml path('')

;with cte1 as
(
  select *,
         row_number() over(partition by ver, fam order by mm) as rn
  from #prd       
),
cte2 as
(
  select ver,
         fam,
         cast(mm as varchar(max)) as mm,
         rn
  from cte1
  where rn = 1
  union all
  select c1.ver,
         c1.fam,
         c2.mm+','+c1.mm,
         c1.rn
  from cte1 as c1
    inner join cte2 as c2
      on c1.rn = c2.rn + 1 and
         c1.ver = c2.ver and
         c1.fam = c2.fam
),
cte3 as
(
  select ver,
         fam,
         mm,
         row_number() over(partition by ver, fam order by rn desc) as rn 
  from cte2
)
select ver,
       fam,
       mm
from cte3
where rn = 1

Upvotes: 3

Samir Adel
Samir Adel

Reputation: 2499

You can use a user defined function that takes the "ver" as a parameter and return the MM comma separated or u can use data

Upvotes: 0

bobs
bobs

Reputation: 22204

You don't need a CTE. Here's a solution that doesn't use the CTE

SELECT ver, fam,
    STUFF((
      SELECT  ', ' + mm
      FROM #prd AS prd
      WHERE p.ver = prd.ver
        AND p.fam = prd.fam
      ORDER BY mm
      FOR XML PATH('')
    ), 1, 2, '') AS MM
FROM #prd AS p
GROUP BY ver, fam

If you need it to be a CTE, try this

;WITH prd_CTE AS
    (
    SELECT ver, fam,
        STUFF((
          SELECT  ', ' + mm
          FROM #prd AS prd
          WHERE p.ver = prd.ver
            AND p.fam = prd.fam
          ORDER BY mm
          FOR XML PATH('')
        ), 1, 2, '') AS MM
    FROM #prd AS p
    GROUP BY ver, fam
    )
SELECT *
FROM prd_CTE

Upvotes: 5

Related Questions