Getting distinct values from multiple lines??

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  December 20, 2018 at 3:08 pm

  #376667

  Hi

  I am trying to get distinct values from multiple filed on multiple lines per "client"

  example
  Client     Field1     Field2     Field3     Field4
  A              1             2             1             3 
  A              2                            4     
  B             3              1            1              3
  B             3               1            3             1
  C              1              1           1              1 

  What I would like to end up with is 
  A  1,2,3 
  B  1,3
  C  1

  Sorry if this doesn't make sense, it's late in the day for me....:)
  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004639

  More actions

  December 20, 2018 at 10:24 pm

  #2017010

  jbalbo - Thursday, December 20, 2018 3:08 PM

  Hi

  I am trying to get distinct values from multiple filed on multiple lines per "client"

  example
  Client     Field1     Field2     Field3     Field4
  A              1             2             1             3 
  A              2                            4     
  B             3              1            1              3
  B             3               1            3             1
  C              1              1           1              1 

  What I would like to end up with is 
  A  1,2,3 
  B  1,3
  C  1

  Sorry if this doesn't make sense, it's late in the day for me....:)
  Thanks

  Your "end up with" for 'A' makes no sense to me unless rows with NULLs in any column are simply to be ignored.  Please explain how you ended up with 1,2,3 for "A" given the inputs you provided.

  Also, this isn't your first ride on this pony.  Help us help you.  Please post your data in a readily consumable format.  See the article at the first link in my signature line under "Helpful Links".  Thanks.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  December 21, 2018 at 4:13 am

  #2017031

  Ignoring the obvious error in the desired results, here is one way of skinning this cat.
  😎

  
USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @SAMPLE_DATA TABLE
(
  CLIENT CHAR(1) NOT NULL
 ,COL01 INT   NULL
 ,COL02 INT   NULL
 ,COL03 INT   NULL
 ,COL04 INT   NULL
);

  INSERT INTO @SAMPLE_DATA (CLIENT,COL01,COL02,COL03,COL04)
VALUES
('A',1,2,1,3)
,('A',2,4,NULL,NULL)
,('B',3,1,1,3)
,('B',3,1,3,1)
,('C',1,1,1,1)
;
;WITH DISTINCT_VALUE_PAIRS AS
(
  SELECT DISTINCT
   X.CLIENT
  ,X.COL_VAL
  FROM  @SAMPLE_DATA  SD
  CROSS APPLY
  (
   SELECT SD.CLIENT, SD.COL01 UNION ALL
   SELECT SD.CLIENT, SD.COL02 UNION ALL
   SELECT SD.CLIENT, SD.COL03 UNION ALL
   SELECT SD.CLIENT, SD.COL04
  ) X(CLIENT,COL_VAL)
  WHERE X.COL_VAL IS NOT NULL
)
SELECT DISTINCT
  DV.CLIENT
 ,STUFF((SELECT
      CONCAT(CHAR(44),DVP.COL_VAL)
    FROM  DISTINCT_VALUE_PAIRS  DVP
    WHERE DVP.CLIENT = DV.CLIENT
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(10)'),1,1,'') AS COL_VAL
FROM DISTINCT_VALUE_PAIRS  DV
;


  Output
  
CLIENT COL_VAL
------ ----------
     A  1,2,3,4
     B      1,3
     C        1