Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Combining a Query to Include an Additional Column


Combining a Query to Include an Additional Column

Author
Message
wwalkerbout
wwalkerbout
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 45
Hi,

I am trying to work with data from the ProjectServer_Reporting database, which is a part of the Microsoft Project Server 2010 infrastructure. I have now done enough with this data and with SQL Queries in particular, to be dangerous.

Up to this point, I have not worked with multi-value fields in the ProjectServer_Reporting database. I found in a search the information I need to make those work in reports (listing them in a comma separated value column, leveraging a Function).

However, this is where my rusty SQL query skills come into play. I basically don’t know how to exact one column from the separate Function assisted query that I found to include it in the code of my main report dataset (called "dsProject_Detail").

So below, I have listed the code in question, with explantions thoughout on where I'm at.

I have a hunch that I need to somehow do a JOIN to incorporate the 'Business Owner' mutli-value column from my separate query into my overall SSRS primary dataset. If someone is able to show me how that can be done with the code below, it would be a huge help for me.

Any assistance in combining these two queries into one would be most appreciated.

Cheers,

Wayne


1. I found the following Function that will handle the process of listing the multi-value field values in a column:



CREATE FUNCTION [dbo].[GetMultipleValues]
( @EntityUID varchar(255)
)
RETURNS varchar(max)
AS
BEGIN
declare @output varchar(max)
Select @output = COALESCE(@output + ', ', '') + MemberValue
from [MSPLT_Business Owner_Userview] mmu
inner join [MSPCFPRJ_Business Owner_AssociationView] mmuv on (mmu.LookupMemberUID = mmuv.LookupMemberUID)
inner join MSP_EpmProject_UserView mep on (mmuv.EntityUID = mep.ProjectUID)
where mmuv.EntityUID = @EntityUID
return @output
END
GO




2. The accompanying SQL query allows me to pull the detail I need about the ‘Business Owner’ multi-value field that I am using.



SELECT DISTINCT projectname, mmuv.EntityUID,
dbo.GetMultipleValues(mmuv.EntityUID) AS [Business Owner]
FROM msp_epmproject_userview mep
INNER JOIN [MSPCFPRJ_Business Owner_AssociationView] mmuv
ON (mep.ProjectUID = mmuv.EntityUID)




3. This displays fine in a table in SQL Server Management Studio.
4. However, what I would really like is for the column to display in my main Dataset called "dsProject_Detail". The query for that Dataset is:



SELECT p.ProjectUID, p.Project_Status,
p.ProjectName AS [Project Name], p.ProjectModifiedDate AS [Last Modified],
po.ResourceName AS [Owner], p.[Project Category],
p.ProjectStartDate AS [Start], p.ProjectFinishDate AS [Finish],
p.ProjectBaseline0StartDate AS [Baseline Start],
p.ProjectBaseline0FinishDate AS [Baseline Finish],
p.ProjectPercentCompleted AS [Percent Completed],
FROM dbo.MSP_EpmProject_UserView p LEFT OUTER JOIN
dbo.MSP_EpmProjectOwner_OlapView po
WHERE (p.ProjectType = 0 OR p.ProjectType = 5 OR p.ProjectType = 6)




5. I've trimed down the main "dsProject_Detail" dataset query, as it was really long, so I hope I didn't miss out anything important.
laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
Is this what you want?



SELECT p.ProjectUID, p.Project_Status,
p.ProjectName AS [Project Name], p.ProjectModifiedDate AS [Last Modified],
po.ResourceName AS [Owner], p.[Project Category],
p.ProjectStartDate AS [Start], p.ProjectFinishDate AS [Finish],
p.ProjectBaseline0StartDate AS [Baseline Start],
p.ProjectBaseline0FinishDate AS [Baseline Finish],
p.ProjectPercentCompleted AS [Percent Completed],
mmuv.EntityUID,
dbo.GetMultipleValues(mmuv.EntityUID) AS [Business Owner]
FROM dbo.MSP_EpmProject_UserView p
INNER JOIN [MSPCFPRJ_Business Owner_AssociationView] mmuv
ON (p.ProjectUID = mmuv.EntityUID)
LEFT OUTER JOIN
dbo.MSP_EpmProjectOwner_OlapView po -- THERE's NO JOIN CONDITION HERE!
WHERE (p.ProjectType = 0 OR p.ProjectType = 5 OR p.ProjectType = 6)




I've added an inner join to 'dbo.MSP_EpmProject_UserView ' which is the base table for both queries.

The two fields are added at the end, but you can place them in any position.

I assume the missing join condition exists in the real query...
wwalkerbout
wwalkerbout
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 45
Hi Laurie,

Thanks for responding to my post! I think that might be what I'm after, but will try it out in my Virtual Envirnment to make sure. You can tell I'm a rookie, as I can't even tell if things will work unitl I try them out.

It looks like I did leave out the JOIN from the original query.

I'll let you know if I'm successful.

Cheers,

Wayne
laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
Hi

Something to bear in mind:

I used an inner join from dbo.MSP_EpmProject_UserView to [MSPCFPRJ_Business Owner_AssociationView] because that's what's in the code above. If [MSPCFPRJ_Business Owner_AssociationView] hasn't got a match for every record in dbo.MSP_EpmProject_UserView & you don't want to lose any data you may need a left outer join instead.
wwalkerbout
wwalkerbout
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 45
Hi,

I can see my priority reading after 15 years of SQL Dev neglect, is to make sure I have a thorough knowledge of the various types of joins again. Problem is, I don't think I did enough 15 years ago to get a good understanding, so I've got some homework to do.

Can't do a whole lot during my work hours, but I'll certainly be trying this out during my evenings. Thanks again.

Cheers,

Wayne
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1721
Here's a solution that seems to be what you are trying to do...joining one table to another table where the source table has a delimited list of key values in a single column. If this is not what you need, can you post some sample tables and data?


/* Create a test table with a column of delimited values */

CREATE TABLE [dbo].[TestStringInput](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DelimitedStringInput] [VARCHAR](max) NULL,
CONSTRAINT [PK_TestStringInput] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Insert some test values */

INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('234,567,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,456,789')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,567,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,234,456,789,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('890,567,234')
GO

/* Create a test table with dummy data and keys */

CREATE TABLE [dbo].[TestJoinOutPutAlt](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TestValue] [VARCHAR](max) NULL,
CONSTRAINT [PK_TestJoinOutPut] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Insert some test values */

SET IDENTITY_INSERT dbo.TestJoinOutPut ON
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (123,'George Washington')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (234,'John Adams')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (456,'Thomas Jefferson')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (567,'James Madison')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (789,'James Monroe')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (890,'Andrew Jackson')
SET IDENTITY_INSERT dbo.TestJoinOutPut OFF
GO


/* Create a function to join the delimited values */
/* to the second table's keys as a table */

CREATE FUNCTION [dbo].[TestJoinTable]
(
@strList VARCHAR(50)
)
RETURNS @RtnValue TABLE
(
[ID] INT IDENTITY(1,1)
,[KeyValue] INT
,[TestValue] VARCHAR(MAX)
,PRIMARY KEY (ID)
,UNIQUE (ID)
)
AS
BEGIN

DECLARE
@TestValues VARCHAR(MAX)
,@strXML XML

SET @strXML = CONVERT(XML,'<root><item>' + REPLACE(@strList,',','</item><item>')+'</item></root>')

INSERT INTO @RtnValue (KeyValue, TestValue)
SELECT
Nodes.VALUE
,testjoin.TestValue
FROM
(
SELECT
X.t.value('.','VARCHAR(MAX)') AS Value
FROM
@strXML.nodes('/root/item') X (t)
) AS Nodes
INNER JOIN
dbo.TestJoinOutPut AS testjoin
ON [Value] = testjoin.ID
ORDER BY
testjoin.TestValue --optional order by

RETURN

END
GO


SELECT * FROM dbo.TestStringInput
SELECT * FROM dbo.TestJoinOutPut

/* Output to a table from a single value input */

SELECT * FROM dbo.TestJoinTable('890,567,234')


/* Output to a table by joining to the input table */

SELECT * FROM dbo.TestStringInput AS i
CROSS APPLY dbo.TestJoinTable(i.DelimitedStringInput)




wwalkerbout
wwalkerbout
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 45
Hi Steven,

Wow, thanks very much for the sample code! I'll try this out in my Virtual Environment this evening as well.

It's great, from my perspective, to get sample code that I can try out and then work back through the code to see how it works. So I very much appreciate you sharing this.

Cheers,

Wayne
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search