SQL Server query returns multiple line results instead of returning 1 line

Question

Using SQL Server, I have multiple tables:

Assignments

 aID,
 acID,
 clientID,
 userID,
 pos,
 dateOn

AssignmentCarriers

 acID,
 clientID,
 cName,
 isAssignment

Users

 userID,
 fName

Clients

 clientID,
 cName,
 code

Each client has multiple carriers. Each carrier has 3 positions to be filled (the assignment).

I wrote a query that returns the proper information, but instead of returning the assignment on one line with the 3 different positions (for each carrier that assignment has), it returns a line for each position and user.

Here's my query:

SELECT DISTINCT
    c.code,
    c.cName,
    ac.cName,
    (SELECT fname 
     FROM assignments 
     INNER JOIN users ON users.userID = assignments.userID 
     WHERE pos = 1 AND aID = a.aID) AS [User 1],
    (SELECT dateOn 
     FROM assignments 
     WHERE pos = 1 AND aID = a.aID) AS [Date Assigned 1],
    (SELECT fname 
     FROM assignments 
     INNER JOIN users ON users.userID = assignments.userID 
     WHERE pos = 2 AND aID = a.aID) AS [User 2],
    (SELECT dateOn 
     FROM assignments 
     WHERE pos = 2 AND aID = a.aID) AS [Date Assigned 2],
    (SELECT fname 
     FROM assignments 
     INNER JOIN users ON users.userID = assignments.userID 
     WHERE pos = 3 AND aID = a.aID) AS [User 3],
    (SELECT dateOn 
     FROM assignments 
     WHERE pos = 3 AND aID = a.aID) AS [Date Assigned 3]
FROM 
    clients c
INNER JOIN 
    assignments a ON a.clientID = c.clientID
INNER JOIN 
    assignmentCarriers ac ON ac.acID = a.acID
WHERE 
    isAssignment = 'True' 
    AND c.active = 'True'
ORDER BY 
    c.cName

This returns:

HDPT  Home Depot  R+L Domestic  Phil Brown  4/1/2023  Null  Null  Null  Null
HDPT  Home Depot  R+L INTL  Phil Brown  5/12/2000  Null  Null  Null  Null
HDPT  Home Depot  R+L Domestic  Null  Null  Mark Twain  1/22/15  Null  Null
HDPT  Home Depot  R+L INTL  Null  Null  Jen Gump  11/12/12  Null  Null
HDPT  Home Depot  R+L Domestic  Null  Null  Null  Null  Rob Mills  2/2/12
HDPT  Home Depot  R+L INTL  Null  Null  Null  Null  John Smith  12/2/22

The desired output should be something like:

HDPT  Home Depot  R+L Domestic  Phil Brown  4/1/2023  Mark Twain  1/22/15  Rob Mills  2/2/12
HDPT  Home Depot  R+L INTL  Phil Brown  5/12/2000  Jen Gump  11/12/12  John Smith  12/2/22

How do I accomplish this?

Answer 1

Your edit does shed a a little light on the problem, but it does not clarify everything. I've made some pretty broad guesses and assumptions to try and carry it over the minimum threshold so I can hopefully help you grasp some of the basics here.

First a quick note on providing DDL/DML - this is very important when you're asking questions like this, as without knowing what the data is, and how it's stored we're pretty much hooped from the get go.

I prefer to use Table variables for this, because they're portable and require little to no clean up after the fact. Based on your descriptions and the psuedo data you provided I think this is a reasonable guess as what your DDL/DML actually is.

DECLARE @Assignments TABLE (aID INT IDENTITY, acID INT, clientID INT, userID INT, pos INT, dateOn DATE);
DECLARE @AssignmentCarriers TABLE (acID INT IDENTITY, clientID INT, cName NVARCHAR(50), isAssignment BIT);
DECLARE @Users TABLE (UserID INT IDENTITY, fName NVARCHAR(50));
DECLARE @Clients TABLE (clientID INT IDENTITY, cName NVARCHAR(50), code NVARCHAR(10), active BIT);

INSERT INTO @Assignments (acID, clientID, userID, pos, dateOn) VALUES (1, 1, 1, 1, '2023-04-01'),  (2, 1, 1, 2, '2015-01-22'),  (1, 1, 2, 3, '2000-05-12'),  (2, 1, 3, 4, '2012-12-11'),  (1, 1, 4, 5, '2022-02-12'),  (2, 1, 5, 5, '2022-02-12');
INSERT INTO @AssignmentCarriers (clientID, cName, isAssignment) VALUES (1, 'R+L Domestic', 1), (2, 'R+L INTL', 1);
INSERT INTO @Users (fName) VALUES ('Phil Brown'), ('Mark Twain'), ('Jen Gump'), ('Rob Mills'), ('John Smith');
INSERT INTO @Clients (cName, code, active) VALUES ('Home Depot', 'HDPT', 1), ('Home Depot', 'HDPT', 1);

First I defined the tables, with their columns and data types, and then I inserted rows into them representative of the output you gave us. This is just best guess.

acID	clientID	cName	isAssignment
1	1	R+L Domestic	1
2	2	R+L INTL	1

aID	acID	clientID	userID	pos	dateOn
1	1	1	1	1	2023-04-01
2	2	1	1	2	2015-01-22
3	1	1	2	3	2000-05-12
4	2	1	3	4	2012-12-11
5	1	1	4	5	2022-02-12
6	2	1	5	5	2022-02-12

UserID	fName
1	Phil Brown
2	Mark Twain
3	Jen Gump
4	Rob Mills
5	John Smith

clientID	cName	code	active
1	Home Depot	HDPT	1
2	Home Depot	HDPT	1

Now we have something to build a query from.

I'm not sure why you're so insistent on attempting to use in-line subqueries in your select. That's a pretty bad idea from the get go and should be a hint that you're doing something goofy. There's no real reason to even consider needing to do that, from what I can tell.

The very first reason you're having a hard time conceptualizing the result set you want is likely due to it being an anti-pattern. RDMS systems operate in sets and strongly favor the normal forms. Anytime you start to deviate from this it becomes challenging, particular with older engines.

Essentially we have two problems to solve here, and then bring those solutions together to compete your requirement. The first is to get a base result set of the assignments, carriers and clients. Then we want a set of three users who belong to those, but as columns, rather than rows.

We're going to use COMMON TABLE EXPRESSIONS (CTE) to do this to keep a sense of calrity and order around the two parts, and then bring them together. The first CTE aggregates the client, assignment and carriers down to just the two distinct rows. The second finds the first three (in order of userID) users for each of them, and then uses PIVOT to retrieve the fName and dateOn for each and return them as columns.

;WITH assignments AS (
SELECT c.clientID, c.cname, code, ac.cName AS acName, ac.acID
  FROM @Clients c
    INNER JOIN @Assignments a
      ON c.clientID = a.clientID
    INNER JOIN @AssignmentCarriers ac
      ON a.acID = ac.acID
 WHERE c.active = 1
 GROUP BY c.clientID, c.cname, code, ac.cName, ac.acID
), ThreeAssignedUsers AS (
SELECT acID, MAX([1]) AS fName1, MAX([11]) AS dateOn1, MAX([2]) AS fName2, MAX([12]) AS dateOn2, MAX([3]) AS fName3, MAX([13]) AS dateOn3
  FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY ac.acID ORDER BY u.UserID) AS rn1, 10+ROW_NUMBER() OVER (PARTITION BY ac.acID ORDER BY u.UserID) AS rn2, fName, dateOn, ac.acID
          FROM @Assignments a
          INNER JOIN @AssignmentCarriers ac
            ON a.acID = ac.acID
          LEFT OUTER JOIN @Users u
            ON a.userID = u.UserID
         WHERE ac.isAssignment = 1
       ) a
    PIVOT (
           MAX(fName) for rn1 IN ([1],[2],[3])
          ) p
    PIVOT (
           MAX(dateOn) for rn2 IN ([11],[12],[13])
          ) p2
 GROUP BY acID
)

SELECT *
  FROM assignments a
    INNER JOIN ThreeAssignedUsers tau
      ON a.acID = tau.acID;

clientID	cname	code	acName	acID	acID	fName1	dateOn1	fName2	dateOn2	fName3	dateOn3
1	Home Depot	HDPT	R+L Domestic	1	1	Phil Brown	2023-04-01	Mark Twain	2000-05-12	Rob Mills	2022-02-12
1	Home Depot	HDPT	R+L INTL	2	2	Phil Brown	2015-01-22	Jen Gump	2012-12-11	John Smith	2022-02-12

You'll notice here that we had to do two pivots, with hardcoded column names. You can only use a column once in a pivot, so rn1 and rn2 are basically the same thing, just duplicated for each pivot. I added a static 10 to rn2 just to distinguish it.

Finally, the two CTEs are then queried and the result set you're looking for is returned.

There's a bunch of limitations and very likely performance implications that you may run into trying to use this. Far from the least of this is the hard coded column names, and number of columns. You'd need to extend both of the pivots manually to be able to add additional user columns to the result set, and add additional pivots and supporting columns if you wanted to add any additional columns (like last name, or something).

When you have to work this hard at getting RDBMs to produce the results it is probably time to re-think your approach and potentially consider using another tool to return the data - there's plenty of options when it comes to producing reports (which might be what you're attempting?) that give you much better options to manipulate the data to the format and layouts you want it in.

Answer 2

It looks like you're really new to using SQL, so let's start with some fundamentals.

DECLARE @Jobs TABLE (JobID INT IDENTITY, CompanyName NVARCHAR(50), CompanyID INT);
DECLARE @Users TABLE (UserID INT IDENTITY, FirstName NVARCHAR(50), LastName NVARCHAR(50), JobID INT);

INSERT INTO @Jobs (CompanyName, CompanyID) VALUES ('Home Depot', 123), ('Home Depot', 123), ('Home Depot', 123);
INSERT INTO @Users (FirstName, LastName, JobID) VALUES ('Michael', 'Mark', 1), ('Jon', 'Smith', 2), ('Louise', 'Manfield', 3);

These are table variables, and define two virtual objects we can use to do some demonstration. We can use them as if they were actual tables, for the most part.

First we'll just get all the users:

SELECT UserID, FirstName, LastName, JobID
  FROM @Users;

UserID	FirstName	LastName	JobID
1	Michael	Mark	1
2	Jon	Smith	2
3	Louise	Manfield	3

We can simply reference the columns in the table by their column names in our SELECT statement. Now, we want to know which users belong to which job. We know the relationship is defined by the JobID column on the Jobs and Users table. We're also using an ALIAS (u and j) to refer to the tables, since they can (and do) have matching column names

SELECT u.UserID, u.FirstName, u.LastName, u.JobID, j.CompanyID, j.CompanyName
  FROM @Users u
    INNER JOIN @Jobs j
      ON u.JobID = j.JobID 

UserID	FirstName	LastName	JobID	CompanyID	CompanyName
1	Michael	Mark	1	123	Home Depot
2	Jon	Smith	2	123	Home Depot
3	Louise	Manfield	3	123	Home Depot

Now we're passed the basics, we can address your actual question. You'd like a list of folks who work for each company, but on one row.

You've listed SQL-Server 2005, which I really hope is a mistake. There are a bunch of ways to do this in newer versions.

If you have a known number of positions to do this with, you could just join the table three times, enforcing how you want to relate them each time:

SELECT DISTINCT CompanyID, u1.FirstName, u1.LastName, u2.FirstName, u2.LastName, u3.FirstName, u3.LastName
  FROM @Jobs J
    INNER JOIN @Users u1
      ON u1.UserID = 1
    INNER JOIN @Users u2
      ON u2.UserID = 2
    INNER JOIN @Users u3
      ON u3.UserID = 3

CompanyID	FirstName	LastName	FirstName	LastName	FirstName	LastName
123	Michael	Mark	Jon	Smith	Louise	Manfield

When you write a query, you're typically going to be working in sets. In this case you're asking for all the rows for the jobs table, and that's what you're being given back. The start of any query is likely to be a list like this, and then you branch off to fetch other objects which have other information, but each one will constitute a row, until you start to perform some aggregation. In this case you want to aggregate a string. Some folks in the comments mentioned the STRING_AGG function, but you're a loooong way from there in 2005.

Answer 3

Pretty sure that conditional aggregation is what you are after here. Also, I greatly simplified all those subqueries into case expressions. This should produce what you are looking for.

SELECT
    c.code,
    c.cName,
    ac.cName,
    [User 1] = max(case when a.pos = 1 then u.fname end),
    [Date Assigned 1] = max(case when a.pos = 1 then a.dateOn end),
    [User 2] = max(case when a.pos = 2 then u.fname end),
    [Date Assigned 2] = max(case when a.pos = 2 then a.dateOn end),
    [User 3] = max(case when a.pos = 3 then u.fname end),
    [Date Assigned 3] = max(case when a.pos = 3 then a.dateOn end)
FROM clients c
INNER JOIN assignments a ON a.clientID = c.clientID
INNER JOIN assignmentCarriers ac ON ac.acID = a.acID
join Users u on u.userID = a.userID
WHERE 
    isAssignment = 'True' 
    AND c.active = 'True'
group by c.code,
    c.cName,
    ac.cName,
ORDER BY 
    c.cName