Count of values in rows

• maheshwaran.it

  Newbie

  Points: 5

  More actions

  February 7, 2015 at 7:04 am

  #148785

  Hi ,

  I have a table data as below

  name A1B1A2B2

  RAHUL PSAP

  name A1B1A2B2 are columns

  RAHUL PSAP are values stored in the table.

  I need to get the total number of 'P' and 'S' and 'A' using select query . Is this possible. Any one please help me out.

  Thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  February 8, 2015 at 1:22 am

  #1776238

  Quick solution, should be enough to get you passed this hurdle

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  DECLARE @SAMPLE_DATA TABLE

  (

  SNAME VARCHAR(15) NOT NULL

  ,A1 CHAR(1) NOT NULL

  ,B1 CHAR(1) NOT NULL

  ,A2 CHAR(1) NOT NULL

  ,B2 CHAR(1) NOT NULL

  );

  INSERT INTO @SAMPLE_DATA(SNAME,A1,B1,A2,B2) VALUES ('RAHUL','P','S','A','P');

  INSERT INTO @SAMPLE_DATA(SNAME,A1,B1,A2,B2) VALUES ('ISAC' ,'A','S','A','P');

  INSERT INTO @SAMPLE_DATA(SNAME,A1,B1,A2,B2) VALUES ('WENDY' ,'A','S','S','S');

  SELECT

  X.SNAME

  ,X.AB

  ,COUNT(*) AS AB_COUNT

  FROM @SAMPLE_DATA SD

  CROSS APPLY (SELECT SNAME, A1 UNION ALL

  SELECT SNAME, B1 UNION ALL

  SELECT SNAME, A2 UNION ALL

  SELECT SNAME, B2) AS X(SNAME,AB)

  GROUP BY X.SNAME

  ,X.AB

  ORDER BY X.SNAME

  ,X.AB

  ;

  Results

  SNAME AB AB_COUNT

  --------------- ---- --------

  ISAC A 2

  ISAC P 1

  ISAC S 1

  RAHUL A 1

  RAHUL P 2

  RAHUL S 1

  WENDY A 1

  WENDY S 3