Sorting to keep people and secretaries together

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  July 12, 2017 at 9:14 am

  #343471

  
CREATE TABLE #tblExperts
        (
        ExpertID int,
        Expert varchar(50)
        )
GOINSERT INTO #tblExperts(ExpertID, Expert)
SELECT 1, 'Jane' UNION ALL
SELECT 2, 'Chris' UNION ALL
SELECT 3, 'Bill'

  SELECT * FROM #tblExperts E

  CREATE TABLE #tblSecretaries
  (
  SecretaryID int,
  ExpertID int,
  Secretary varchar(50)
  )

  GO

  INSERT INTO #tblSecretaries(SecretaryID, ExpertID, Secretary)
  SELECT 1,2, 'Mark' UNION ALL
  SELECT 2,3, 'Tina' UNION ALL
  SELECT 3,1, 'Tom'

  SELECT * FROM (
  SELECT Expert AS [Contact], 'Expert' AS [Role], '' AS [SecretarysExpert] FROM #tblExperts
  UNION
  SELECT Secretary AS [Contact], 'Secretary' AS [Role], Expert AS [SecretarysExpert]
  FROM #tblSecretaries S
  INNER JOIN #tblExperts E ON S.ExpertID = E.ExpertID) A

  DROP TABLE #tblExperts
  DROP TABLE #tblSecretaries

  GO

  (I think) in the tables above we have some experts and some secretaries
  Chris has a secretary called Mark
  Bill has a secretary called Tina
  Jane has a secretary called Tom

  I want to return a list of Experts and their Secretaries. But I need it sorted alphabetically by Expert but with each Expert's secretary appearing after their expert

  So, the output should look like:
  Contact ... Role ............. SecretarysExpert
  Bill ........... Expert .........
  Tina ........ Secretary ....  Bill
  Chris ....... Expert .........
  Mark ....... Secretary ..... Chris
  Jane ....... Expert ...........
  Tom ........ Secretary ...... Jane

  So, thinking about the experts, they need to appear in alphabetical order, so Bill - Chris - Jane
  with each expert's secretary below the expert record.

  How can I sort this to make that happen?

• pietlinden

  SSC Guru

  Points: 63198

  More actions

  July 12, 2017 at 9:18 am

  #1950574

  What are you going to use this for? I'm asking because in SSRS, this is really simple. You would add a tablix to your report surface, and just group by Expert.

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  July 12, 2017 at 9:24 am

  #1950575

  pietlinden - Wednesday, July 12, 2017 9:18 AM

  What are you going to use this for? I'm asking because in SSRS, this is really simple.

  It's going to be a stored procedure that is going to return a list of Experts/Secretaries/Their email addresses - and whether the expert is to be contacted, or their secretary, or both, to be called by a asp.net web application.

• Thom A

  SSC Guru

  Points: 99009

  More actions

  July 12, 2017 at 9:27 am

  #1950577

  A simple change would be:
  SELECT Contact, Role, SecretarysExpert
FROM (
SELECT ExpertID, Expert AS [Contact], 'Expert' AS [Role], '' AS [SecretarysExpert] FROM #tblExperts
UNION
SELECT E.ExpertID, Secretary AS [Contact], 'Secretary' AS [Role], Expert AS [SecretarysExpert]
FROM #tblSecretaries S
  INNER JOIN #tblExperts E ON S.ExpertID = E.ExpertID) A
ORDER BY ExpertID;

  Personally, however, if you have access to the database design, I would actually recommend using a self referencing table. Something like:
  --Create table
CREATE TABLE #Contact
  (ContactID int NOT NULL,
  Contact varchar(50) NOT NULL,
  Role varchar(10),
  ExpertID int NULL);
GO
--Sample Data
INSERT INTO #Contact
VALUES
  (1, 'Jane', 'Expert', NULL),
  (2, 'Chris', 'Expert', NULL),
  (3, 'Bill', 'Expert', NULL),
  (4, 'Mark', 'Secretary', 2),
  (5, 'Tina', 'Secretary', 3),
  (6, 'Tom', 'Secretary', 1);
GO
--Sample Select
SELECT C1.Contact,
   C1.Role,
   C2.Contact AS Expert
FROM #Contact C1
  LEFT JOIN #Contact C2 ON C1.ExpertID = C2.ContactID
ORDER BY ISNULL(C2.ContactID, C1.ContactID) DESC, Role;
GO
--Clean up
DROP TABLE #Contact;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  July 12, 2017 at 10:04 am

  #1950584

  Thom A - Wednesday, July 12, 2017 9:27 AM

  A simple change would be:
  SELECT Contact, Role, SecretarysExpert
FROM (
SELECT ExpertID, Expert AS [Contact], 'Expert' AS [Role], '' AS [SecretarysExpert] FROM #tblExperts
UNION
SELECT E.ExpertID, Secretary AS [Contact], 'Secretary' AS [Role], Expert AS [SecretarysExpert]
FROM #tblSecretaries S
  INNER JOIN #tblExperts E ON S.ExpertID = E.ExpertID) A
ORDER BY ExpertID;

  Personally, however, if you have access to the database design, I would actually recommend using a self referencing table. Something like:
  --Create table
CREATE TABLE #Contact
  (ContactID int NOT NULL,
  Contact varchar(50) NOT NULL,
  Role varchar(10),
  ExpertID int NULL);
GO
--Sample Data
INSERT INTO #Contact
VALUES
  (1, 'Jane', 'Expert', NULL),
  (2, 'Chris', 'Expert', NULL),
  (3, 'Bill', 'Expert', NULL),
  (4, 'Mark', 'Secretary', 2),
  (5, 'Tina', 'Secretary', 3),
  (6, 'Tom', 'Secretary', 1);
GO
--Sample Select
SELECT C1.Contact,
   C1.Role,
   C2.Contact AS Expert
FROM #Contact C1
  LEFT JOIN #Contact C2 ON C1.ExpertID = C2.ContactID
ORDER BY ISNULL(C2.ContactID, C1.ContactID) DESC, Role;
GO
--Clean up
DROP TABLE #Contact;


  Thanks for your reply. I'd already tried your initial code - including and sorting by ExpertID. This does get the experts and secretaries together, but the experts are not in alphabetical order. They appear ordered by ExpertID which in this dataset orders the experts as Jane, Chris, Bill

  I do have access to the database structure, but I don't want to change from having the Experts in one table and the Secretaries in another.

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  July 12, 2017 at 10:12 am

  #1950585

  webskater - Wednesday, July 12, 2017 10:04 AM

  Thom A - Wednesday, July 12, 2017 9:27 AM

  A simple change would be:
  SELECT Contact, Role, SecretarysExpert
FROM (
SELECT ExpertID, Expert AS [Contact], 'Expert' AS [Role], '' AS [SecretarysExpert] FROM #tblExperts
UNION
SELECT E.ExpertID, Secretary AS [Contact], 'Secretary' AS [Role], Expert AS [SecretarysExpert]
FROM #tblSecretaries S
  INNER JOIN #tblExperts E ON S.ExpertID = E.ExpertID) A
ORDER BY ExpertID;

  Personally, however, if you have access to the database design, I would actually recommend using a self referencing table. Something like:
  --Create table
CREATE TABLE #Contact
  (ContactID int NOT NULL,
  Contact varchar(50) NOT NULL,
  Role varchar(10),
  ExpertID int NULL);
GO
--Sample Data
INSERT INTO #Contact
VALUES
  (1, 'Jane', 'Expert', NULL),
  (2, 'Chris', 'Expert', NULL),
  (3, 'Bill', 'Expert', NULL),
  (4, 'Mark', 'Secretary', 2),
  (5, 'Tina', 'Secretary', 3),
  (6, 'Tom', 'Secretary', 1);
GO
--Sample Select
SELECT C1.Contact,
   C1.Role,
   C2.Contact AS Expert
FROM #Contact C1
  LEFT JOIN #Contact C2 ON C1.ExpertID = C2.ContactID
ORDER BY ISNULL(C2.ContactID, C1.ContactID) DESC, Role;
GO
--Clean up
DROP TABLE #Contact;


  Thanks for your reply. I'd already tried your initial code - including and sorting by ExpertID. This does get the experts and secretaries together, but the experts are not in alphabetical order. They appear ordered by ExpertID which in this dataset orders the experts as Jane, Chris, Bill

  I do have access to the database structure, but I don't want to change from having the Experts in one table and the Secretaries in another.

  I seem to have found the answer ...

  


  SELECT ExpertID, Contact, Role, SecretarysExpert

  FROM (

  SELECT ExpertID, Expert AS [Contact], 'Expert' AS [Role], Expert AS [SecretarysExpert] FROM #tblExperts

  UNION

  SELECT E.ExpertID, Secretary AS [Contact], 'Secretary' AS [Role], Expert AS [SecretarysExpert]

  FROM #tblSecretaries S

  INNER JOIN #tblExperts E ON S.ExpertID = E.ExpertID) A

  ORDER BY SecretarysExpert;

  


• Thom A

  SSC Guru

  Points: 99009

  More actions

  July 12, 2017 at 10:14 am

  #1950589

  webskater - Wednesday, July 12, 2017 10:04 AM

  Thom A - Wednesday, July 12, 2017 9:27 AM

  A simple change would be:
  SELECT Contact, Role, SecretarysExpert
FROM (
SELECT ExpertID, Expert AS [Contact], 'Expert' AS [Role], '' AS [SecretarysExpert] FROM #tblExperts
UNION
SELECT E.ExpertID, Secretary AS [Contact], 'Secretary' AS [Role], Expert AS [SecretarysExpert]
FROM #tblSecretaries S
  INNER JOIN #tblExperts E ON S.ExpertID = E.ExpertID) A
ORDER BY ExpertID;

  Personally, however, if you have access to the database design, I would actually recommend using a self referencing table. Something like:
  --Create table
CREATE TABLE #Contact
  (ContactID int NOT NULL,
  Contact varchar(50) NOT NULL,
  Role varchar(10),
  ExpertID int NULL);
GO
--Sample Data
INSERT INTO #Contact
VALUES
  (1, 'Jane', 'Expert', NULL),
  (2, 'Chris', 'Expert', NULL),
  (3, 'Bill', 'Expert', NULL),
  (4, 'Mark', 'Secretary', 2),
  (5, 'Tina', 'Secretary', 3),
  (6, 'Tom', 'Secretary', 1);
GO
--Sample Select
SELECT C1.Contact,
   C1.Role,
   C2.Contact AS Expert
FROM #Contact C1
  LEFT JOIN #Contact C2 ON C1.ExpertID = C2.ContactID
ORDER BY ISNULL(C2.ContactID, C1.ContactID) DESC, Role;
GO
--Clean up
DROP TABLE #Contact;


  Thanks for your reply. I'd already tried your initial code - including and sorting by ExpertID. This does get the experts and secretaries together, but the experts are not in alphabetical order. They appear ordered by ExpertID which in this dataset orders the experts as Jane, Chris, Bill

  I do have access to the database structure, but I don't want to change from having the Experts in one table and the Secretaries in another.

  Sorry, hadn't noticed that, actually then modifing your existing query (note I have replaced '' with NULL):
  SELECT * FROM (
SELECT Expert AS [Contact], 'Expert' AS [Role], NULL AS [SecretarysExpert] FROM #tblExperts
UNION
SELECT Secretary AS [Contact], 'Secretary' AS [Role], Expert AS [SecretarysExpert]
FROM #tblSecretaries S
INNER JOIN #tblExperts E ON S.ExpertID = E.ExpertID) A
ORDER BY ISNULL([SecretarysExpert], [Contact]),Contact;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk