Query Help - Merge multipe rows into one field/row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help - Merge multipe rows into one field/row

Author

Topic

tdott
Starting Member

2 Posts

Posted - 2009-08-05 : 10:29:39

I am trying to build a report, the problem i am running into is when i add this one field from another table, it duplicates the rows for each item in that table. I want just one row, but either all the items in one field or the fields listed horizontally.

Example of data.

ProjectName Project# TeamMemberName TimeSheetHours
ABC 123 Tim A. 44.5
ABC 123 John. B. 66.7
ABC 123 Dave C. 55.5
BCD 222 Tim A. 11.1
BCD 222 John. B. 22.2
Now costcenters table is linked by Project#:
Sameple Data:

CostCenterID Project# Name
00000000000001 123 CostCenterA
00000000000002 123 CostCenterB
00000000000003 222 CostCenterB

Now when i try to add the cost center table to get the sames of each cost center it duplicates each row, skewing the timesheeting numbers i'm trying to get.

I'd like to get the costcenters in the same row, either in one field seperated by a comma (CostCenterA, CostCenterB) or in horizational fields. The problem is there are multiple cost centers linked to the Project, they are in another table with a row for each CC. CostCenters can be anywhere from 1-15 per project.

How can i do this?

Hopefully that gives enough info. I've tried to make it simple, let me if you need more info.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 10:52:19

One way is to exclude the CostCenter table from your query but create a user defined function that will return a comma seperated list of costCenters for a given project. Then select the function as one of your columns. You can see examples of this type of function by searching for "CSV" or "concatenate function"

Here's one:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293

Be One with the Optimizer
TG

tdott
Starting Member

2 Posts

Posted - 2009-08-05 : 16:39:25

Thank you, that was of great help!! The northwind example helped this newbie out.

quote:
I owe you the blood of my first born.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources