Need Output on a Sql Queries

Question

I have a Sql table:

PLAN_CD PLAN_NM CLASS
D01501  DENTAL  AA
D01501  DENTAL  AB
D01501  DENTAL  AC
V01501  VISION  AA
V01501  VISION  AB

Output should be:

PLAN_CD PLAN_NM
D01501  DENTAL,AA,AB,AC
V01501  VISION,AA,AB

You don't need "an output". You need to do some coding. – James Z Oct 18 '16 at 18:14 — James Z, Oct 18 '16 at 18:14

score 0 · Answer 1 · edited May 23 '17 at 12:33

0

Following 2 Questions might help you:

How to use GROUP BY to concatenate strings in SQL Server?

GROUP BY to combine/concat a column

Have a look.

edited May 23 '17 at 12:33

Community

1
1

answered Oct 18 '16 at 18:43

gschambial

1,383
2
11
22

score 0 · Answer 2 · answered Oct 18 '16 at 18:48

In MSSQL 2005 and up you can do:

select * into #data 
from (
    select 'D01501' as PLAN_CD, 'DENTAL' as PLAN_NM, 'AA' as CLASS union
    select 'D01501', 'DENTAL', 'AB' union
    select 'D01501', 'DENTAL', 'AC' union
    select 'V01501', 'VISION', 'AA' union
    select 'V01501', 'VISION', 'AB'
) x


select R.PLAN_CD, PLAN_NM = R.PLAN_NM + ',' + 
STUFF((
    SELECT ',' + [CLASS] 
    FROM #data 
    WHERE (PLAN_CD = R.PLAN_CD and PLAN_NM = R.PLAN_NM) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,1,'')

from
#data R
group by R.PLAN_CD, R.PLAN_NM

This will group on the PLAN_CD and PLAN_NM fields and concatenate the CLASS field as selected with a subquery. Concatenation is done using FOR XML construction.

Need Output on a Sql Queries

2 Answers2