SQLTeam.com | Weblogs | Forums

MS SQL - Multiple Select Statements for Dashboard Type Presentation of Result Set

Hello, Everyone!

I'd like to craft a query that will allow me to display in separate columns the results of a few subqueries.

It'll end up being a result set that makes up a sort of rudimentary dashboard.

I'm close, but the multiple return nature of those subqueries stops me where I am so far.

Here's what I'm working with now:

(select a.Field1 from ssfields A where a.Field6 = 'On Hold') as "On Hold",
(select a.Field1 from ssfields A where a.Field6 = 'Approved') as "Approved"
from ssfields A
where a.Field1=a.Field1

I get this error when run:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I've tried joins in quite a few different forms to try to get around this, and so far I'm coming up blank.

It seems like going to a join usually solves the multiple return issue for me, but not this time.

Here's the relevant makeup of the ssFields table that I'm referencing:

Field1 Field6
ABC Coffee On Hold
Staples Arrived
PerfectSolutions Arrived
ABC Coffee On Hold
SoftCo. On Hold
SoftCo. Approved
PerfectSolutions Arrived
Staples Approved
Staples Arrived

So, in the end, what I'm trying to get is columns for Arrived, On Hold, etc. that are populated with the values in Field1 that are a match for that Field6 value.

Thank You for looking, and I really look forward to hearing what you've got to say!

For the sample data that you posted, what is the exact output that you are trying to get?


I'd like something like this, except instead of showing up in two different result sets the results would appear in their own columns: