Pivot Help

• rjjh78

  SSCrazy

  Points: 2784

  More actions

  August 24, 2016 at 9:42 am

  #316659

  Hello,

  I am trying to use the following data structure to present the data to show each Title and Count by LocID but cannot seem to get it right. Should I be using a Pivot table for this or do I need to do something else?

  Present Data to look like this:

  RegionDistrictLocIDDirectorIT ManagerSupport TechDeveloperDBABusiness Analyst

  112004014211

  112007018212

  1120131210424

  Table Structure and Data Sample:

  CREATE TABLE #Loc (Cnt int, Region int, District int, LocID int, Title varchar(25))

  INSERT INTO #Loc VALUES ('1','1','1','2004','IT Manager');

  INSERT INTO #Loc VALUES ('4','1','1','2004','Support Tech');

  INSERT INTO #Loc VALUES ('2','1','1','2004','Developer');

  INSERT INTO #Loc VALUES ('1','1','1','2004','DBA');

  INSERT INTO #Loc VALUES ('1','1','1','2004','Business Analyst');

  INSERT INTO #Loc VALUES ('1','1','1','2007','IT Manager');

  INSERT INTO #Loc VALUES ('8','1','1','2007','Support Tech');

  INSERT INTO #Loc VALUES ('2','1','1','2007','Developer');

  INSERT INTO #Loc VALUES ('1','1','1','2007','DBA');

  INSERT INTO #Loc VALUES ('2','1','1','2007','Business Analyst');

  INSERT INTO #Loc VALUES ('1','1','1','2013','IT Director');

  INSERT INTO #Loc VALUES ('2','1','1','2013','IT Manager');

  INSERT INTO #Loc VALUES ('10','1','1','2013','Support Tech');

  INSERT INTO #Loc VALUES ('4','1','1','2013','Developer');

  INSERT INTO #Loc VALUES ('2','1','1','2013','DBA');

  INSERT INTO #Loc VALUES ('4','1','1','2013','Business Analyst');

  Can someone offer some advice? Thank you in advance.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  August 24, 2016 at 9:53 am

  #1897238

  You can use Cross tabs.

  SELECT Region, District, LocID,

  MAX(CASE WHEN Title = 'IT Director' THEN Cnt ELSE 0 END),

  MAX(CASE WHEN Title = 'IT Manager' THEN Cnt ELSE 0 END)

  --etc

  FROM #Loc

  GROUP BY Region, District, LocID;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2