Column to contain value from multiple rows

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

General SQL Server Forums

New to SQL Server Programming

Column to contain value from multiple rows

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-01-24 : 06:11:53

Hi,

I'm in the process of creating a report for one of our clients but I'm stumped on one of the requirements. So what I'm trying to do makes sense I'll provide the following tables and data:


CREATE TABLE #CASES (
	CASEID INT,
	CASEREF VARCHAR(10)
)

CREATE TABLE #CASENAME (
	CASEID INT,
	NAMENO INT,
	NAMETYPE CHAR(1)

)

CREATE TABLE #NAME (
	NAMENO INT,
	NAMECODE VARCHAR(100)
)

INSERT INTO #CASES VALUES (1, 'P01')

INSERT INTO #CASENAME VALUES (1, 001, 'O')
INSERT INTO #CASENAME VALUES (1, 002, 'O')

INSERT INTO #NAME VALUES (001, 'OWNER 1')
INSERT INTO #NAME VALUES (002, 'OWNER 2')

SELECT C.CASEREF, N.NAMECODE
FROM #CASES C
INNER JOIN #CASENAME CN ON CN.CASEID = C.CASEID AND CN.NAMETYPE = 'O'
INNER JOIN #NAME N ON N.NAMENO = CN.NAMENO

When I run this query two rows are returned for the same case but our client only wants one row per case. I'm not sure if/how I can get the name of the second owner (if there is one) to be combined in a column with the first owner (there should always be one).

Any ideas how I could go about this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 10:17:17

so how should your output be? what if there are more than 2 owners? you want all values in same column or as separate columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-01-24 : 10:48:16

I guess if there was more than two then yes they should appear in the same column if possible. The output would ideally look something like (from the data above):

CASEREF | OWNERS
P01 | OWNER 1, OWNER 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 10:49:34

are you using SQL 2005 or above?

try below and post result

SELECT @@VERSION

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-01-24 : 10:56:29

I'm using 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:01:44

cool. then use below


SELECT c.CASEREF,
STUFF((SELECT TOP 100 PERCENT ',' + n.NAMECODE FRON #NAMES n INNER JOIN #CASENAME cn ON cn.NAMENO = n.NAMENO WHERE cn.CASEID = c.CASEID ORDER BY n.NAMENO FOR XML PATH('')),1,1,'') AS OWNERS
FROM #CASES c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-01-24 : 11:04:21

Ah ok, that works perfectly.

Thanks very much for the help. I can have a look on BOL for information on how this works.

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:09:44

ok. you're welcome

let me know if you had difficulty understanding any part of it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources