asked on

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

Guy Hengel [angelIII / a3]

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...

jnsimex

ASKER

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

Ephraim Wangoya

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

lludden

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.

Sharath S

What is your SQL Server version?

jnsimex

ASKER

@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

ASKER CERTIFIED SOLUTION

lludden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial