paired columns data arranging in asp.net

Question

**CODE**    **TYPE**
 --------------------
  XXOPT      POD
  XXOPT      FPOD
  MSC        OPR
  KLM        OPR
  40DV       SZTP
  90DV       SZTP
  MMVD       POD
  KKLP       FPOD
  SSRG       FPOD

I HAVE A DATA TABLE COMING FROM DATA BASE

I WANT TO SHOW MY RESULT LIKE FOLLOWING FORMAT

NOTE: HERE POD,FPOD,OPR,SZTP ARE STATIC TYPES

POD:  XXOPT,MMVD 
FPOD: XXOPT,KKLP,SSRG
OPR:  MSC,KLM  
SZTP: 40DV, 90DV

  WILL YOU PLESSE HELP ME TO SHOW ABOVE FORMAT AS OUT PUT.

Bogdan Sahlean · Accepted Answer · 2013-07-23T17:23:50.230

DECLARE @MyTable TABLE(
    [CODE]  NVARCHAR(50) NOT NULL,
    [TYPE]  NVARCHAR(50) NOT NULL
);
INSERT  @MyTable ([CODE],[TYPE])
SELECT 'XXOPT','POD'
UNION ALL SELECT 'XXOPT','FPOD'
UNION ALL SELECT 'MSC','OPR'
UNION ALL SELECT 'KLM','OPR'
UNION ALL SELECT '40DV','SZTP'
UNION ALL SELECT '90DV','SZTP'
UNION ALL SELECT 'MMVD','POD'
UNION ALL SELECT 'KKLP','FPOD'
UNION ALL SELECT 'SSRG','FPOD';

SELECT  x.[TYPE],y.GROUP_CONCAT
FROM    (SELECT [TYPE] FROM @MyTable GROUP BY [TYPE]) x
CROSS APPLY(
    SELECT STUFF((SELECT    ','+y.CODE
    FROM    @MyTable y
    WHERE   y.[TYPE]=x.[TYPE]
    FOR XML PATH('')),1,1,'') AS GROUP_CONCAT
)y;

Results:

TYPE GROUP_CONCAT
---- ---------------
FPOD XXOPT,KKLP,SSRG
OPR  MSC,KLM
POD  XXOPT,MMVD
SZTP 40DV,90DV

Note: In this case you need an index on ([TYPE])+INCLUDE([CODE]) or an index on ([TYPE],[CODE]).

paired columns data arranging in asp.net

1 Answers1