Query to return values side by side from one table

Hi There,

I am trying to put 2 custom values side by side in the query.

The first custom value which is from the Projects Table (2 segment code xxxxx-xxx) and the second custom value is from the Tasks Table (xxx). These custom values are stored in the
CustomValue Table.



Thanks.


 
SELECT     EMPLOYEE.FIRST + ' ' + EMPLOYEE.LAST AS NAME, CLIENT.NAME AS CLIENT, PROJECT.NAME AS PROJECT, TASK.NAME AS TASK, 
                      [GROUP].NAME AS TEAM, TRANS.DATE, CUSTOMVALUE.VALUE
FROM         PROJECT INNER JOIN
                      TRANS ON PROJECT.ID = TRANS.PROJECT INNER JOIN
                      TASK ON TRANS.TASK = TASK.ID INNER JOIN
                      EMPLOYEE ON TRANS.EMPLOYEE = EMPLOYEE.ID INNER JOIN
                      [GROUP] ON TRANS.[GROUP] = [GROUP].ID LEFT OUTER JOIN
                      CUSTOM INNER JOIN
                      CUSTOMVALUE ON CUSTOM.ID = CUSTOMVALUE.CUSTOM INNER JOIN
                      CUSTOMTEMPLATE ON CUSTOM.CUSTOMTEMPLATE = CUSTOMTEMPLATE.ID ON PROJECT.ID = CUSTOM.LINKID AND 
                      TASK.ID = CUSTOM.LINKID LEFT OUTER JOIN
                      CLIENT ON TRANS.PROJECT = CLIENT.ID
WHERE     (CUSTOMVALUE.VALUE IS NOT NULL)
ORDER BY NAME

Open in new window

jnsimexAsked:
Who is Participating?
 
lluddenCommented:
Here is what I have done in SQL to fix a similar situation.  We have multiple drivers each with multiple stops and those stops pass through one or more 'regions' each day.  I needed a query to show each driver and all the regions they pass though on each date.

So first a function to create a list of all the regions for a driver for a day:



CREATE FUNCTION [dbo].[RegionsForRoute] (@RouteNo int, @TransportDate datetime)
RETURNS varchar(200) AS  
BEGIN
Declare @RegionList varchar(200)

SELECT     @RegionList = coalesce(@RegionList+',','') + CAST(RegionID AS varchar(10))
FROM Transport
WHERE [TransportDate] = @TransportDate AND [RouteNo] = @RouteNo
GROUP BY RegionID

Return (@RegionList)
END

Then I do my query:

SELECT Employee.Name, Route.RouteDescription, RoutesPerDay.TransportDate,  dbo.RegionsForRoute(RoutesPerDay.RouteID, RoutesPerDay.TransportDate)
FROM RoutesPerDay
    INNER JOIN Employee ON RoutesPerDay.DriverID = Employee.EmployeeID
    INNER JOIN Routes ON RoutesPerDay.RouteID = Routes.RouteID

This returns
Tom Smith, Local Delivery, 2011-03-01, "NE,SW,West"
Bill Wilson, Local Delivery, 2011-03-01, "East"
etc

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you please clarify with data samples what you have in the tables (1-2 records), and the requested output?
to avoid guessing, it's not 100% clear...
0
 
jnsimexAuthor Commented:
Sure, not a problem.

The output of the query return this:

Alex Gil,A: Ripleys Williamsburg,CIP,Mechanical Design,S / C / Eng,2010-08-13 00:00:00.000,17700-CPZ
Alex Gil,A: Ripleys Williamsburg,CIP,Mechanical Design,S / C / Eng,2010-08-13 00:00:00.000,710

The first custom value is "17700-CPZ" and the second custom value is "710"

I would like the output of the query to return this instead:

Alex Gil,A: Ripleys Williamsburg,CIP,Mechanical Design,S / C / Eng,2010-08-1300:00:00.000,17700-CPZ,710
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ephraim WangoyaCommented:
Just add the field to your result set

SELECT     EMPLOYEE.FIRST + ' ' + EMPLOYEE.LAST AS NAME, CLIENT.NAME AS CLIENT, PROJECT.NAME AS PROJECT, TASK.NAME AS TASK,
                      [GROUP].NAME AS TEAM, TRANS.DATE, CUSTOMVALUE.VALUE, PROJECT.VALUE
FROM         PROJECT INNER JOIN
                      TRANS ON PROJECT.ID = TRANS.PROJECT INNER JOIN
                      TASK ON TRANS.TASK = TASK.ID INNER JOIN
                      EMPLOYEE ON TRANS.EMPLOYEE = EMPLOYEE.ID INNER JOIN
                      [GROUP] ON TRANS.[GROUP] = [GROUP].ID LEFT OUTER JOIN
                      CUSTOM INNER JOIN
                      CUSTOMVALUE ON CUSTOM.ID = CUSTOMVALUE.CUSTOM INNER JOIN
                      CUSTOMTEMPLATE ON CUSTOM.CUSTOMTEMPLATE = CUSTOMTEMPLATE.ID ON PROJECT.ID = CUSTOM.LINKID AND
                      TASK.ID = CUSTOM.LINKID LEFT OUTER JOIN
                      CLIENT ON TRANS.PROJECT = CLIENT.ID
WHERE     (CUSTOMVALUE.VALUE IS NOT NULL)
ORDER BY NAME
0
 
lluddenCommented:
Are there just 2, or will there be different values for each row?

If there are a variable number, then it will be difficult to do in separate fields.  You could combine all of the custom values for each person into a single delimited field.

If that is acceptable, create a function that takes a custom.ID and returns a comma delimited list of customvalue.value, then call that function in the query instead of doing a join to the customvalue table.
0
 
SharathData EngineerCommented:
What is your SQL Server version?
0
 
jnsimexAuthor Commented:
@lludden

There are more than 2 row and could be different values for each row based on the Project (17700-CPZ) and the task (710)

@Sharath 123

SQL Server Express V.9
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.