• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

multiple values 1 column and combining 2 results

I am trying to get inforamtion for a user to include if they have multiple managers.
The first half of the query gets the user information
the second half gets the managers they have could be as many as 1,2,or 3 (what ever)

The first Select returns
user1                  406      22      2
user2                  406      14      1
                    406      16      378
user3                  406      17      391
user4                  406      18      385
user5                  406      19      395
user6                  406      20      390
user7                  406      24      425
user8                  406      25      410

The second select returns

Manager1,
Manager2,

It is obvious my coalesce is not working and I need to find a way to make that work and
put it on the same line for each user

In this case it would look like this
UserName                mgrid  uniquid userid   managers
user2                  406      14      1      Manager1, Manager2

Select User.FirstNm + ' ' + User.LastNm as userfullname,ManagerStaff.mgruseruid,ManagerStaff.mgrstaffuid,ManagerStaff.staffuseruid 
FROM User User 
JOIN ManagerStaff ManagerStaff ON ManagerStaff.staffuseruid = User.UserUID 
WHere User.useractive = 1 
AND ManagerStaff.mgruseruid = 406

Select COALESCE(a.FirstNm + ' ' + a.LastNm + ', ', '') as managers
FROM User a 
JOIN ManagerStaff b ON b.mgruseruid = a.UserUID 
WHere a.useractive = 1 
and b.staffuseruid = 1

Open in new window


Can I get a little help please
0
r3nder
Asked:
r3nder
1 Solution
 
RehanYousafCommented:
Which SQL Server you are using?

Also if you provide some sample data for both tables
1. ManagerStaff
2. User

it will help
0
 
r3nderAuthor Commented:
Manager staff
1      1      391
2      1      396
3      1      385
4      1      378

User
1      user      first      last      email      pass      8F1OvdqC7nuT+CmoSMpGLtnLpcZlgYrhjP4a0zfhNXi8CiNG4pyRfBYM4zV2GATG5JcmGnOzh+ccaWzPxvNB6Q==      -343587002      1      0      0      True      False      2013-01-15 07:36:16.000      2013-01-15 07:36:20.000      4445      DDD      2012-03-26 15:37:52.657      2010-09-23 20:11:48.457      True
2      user2      first      last      email       pass      charles      sNtoKSRhExtuB91ge8vtNHfKxKUSATBwcEqvQfR/FNisL3orWqjhAPBVf17+OBPaZnjBGDwHbBYnieIe3ywAxA==      531298800      10      0      0      True      False      2009-08-31 12:52:00.000      2009-12-15 07:43:00.000      261      DDD      2012-08-24 14:10:30.527      2010-09-23 20:11:48.770      False
3      user3      first      last      email      pass      FLgtcB3Yp2Opxi8vyFRyBR7A5SxElInCbgg+F2xWFXFWYDx815DmL+1q5IbgiiTdbF2XnJhIzHdQ3ucajwwvXQ==      1640816147      10      0      0      True      True      2011-06-15 10:00:24.000      2011-06-15 08:06:35.000      79      DDD      2010-09-23 20:11:48.817      2010-09-23 20:11:48.817      False
0
 
r3nderAuthor Commented:
this

Select User.FirstNm + ' ' + User.LastNm as userfullname,ManagerStaff.mgruseruid,ManagerStaff.mgrstaffuid,ManagerStaff.staffuseruid,'' as managers
FROM User User
JOIN ManagerStaff ManagerStaff ON ManagerStaff.staffuseruid = User.UserUID
WHere User.useractive = 1
AND ManagerStaff.mgruseruid = 406
UNION
Select '','','','',COALESCE(a.FirstNm + ' ' + a.LastNm + ', ', '') as managers
FROM User a
JOIN ManagerStaff b ON a.UserUID = b.mgruseruid
WHere a.useractive = 1
and a.UserUID in (select staffuseruid FROM ManagerStaff)

gets this

                  0      0      0      mngr1,
                  0      0      0      mngr2,
                   406      16      378      
first1 last1            406      22      2      
first2 last2            406      25      410      
first3 last3            406      24      425      
first4 last4            406      18      385      
first5 last5            406      14      1      
first6 last6            406      17      391      
first7 last7            406      20      390      
Test Test            406      19      395      

should be


first1 last1            406      22      2      
first2 last2            406      25      410      
first3 last3            406      24      425      
first4 last4            406      18      385      
first5 last5            406      14      1      mng1,mngr2
first6 last6            406      17      391      
first7 last7            406      20      390      
Test Test            406      19      395
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
RehanYousafCommented:
And which SQL Serevr you are working on (2000, 2005, 2008)

if 2005 or above then we can use FOR XML for this

EDIT: Also if you put the data with their relative filed names .. it will be lot lot easier ... ideally in excel
0
 
r3nderAuthor Commented:
2008 r2 - Please help
Book1.xlsx
0
 
awking00Commented:
Given just the values for the managerstaff and user tables in your spreadsheet, Book1.xlsx, what do you want to see for results?
0
 
r3nderAuthor Commented:
first5 last5            406      14      1      mng1,mngr2
userfullname(first + last)-mgruseruid - mgrstaffuid - staffuseruid - list of managers
The first half of the query returns
userfullname(first + last)-mgruseruid - mgrstaffuid - staffuseruid
0
 
r3nderAuthor Commented:
I am trying to return this for a dataset to use in a grid
0
 
r3nderAuthor Commented:
This gives me who they are associated to

Select User.FirstNm + ' ' + User.LastNm as userfullname,ManagerStaff.mgruseruid,ManagerStaff.mgrstaffuid,ManagerStaff.staffuseruid,'' as managers
FROM User User
JOIN ManagerStaff ManagerStaff ON ManagerStaff.staffuseruid = User.UserUID
WHere User.useractive = 1
AND ManagerStaff.mgruseruid = 406
UNION
Select '','','',b.staffuseruid,Coalesce(a.FirstNm + ' ' + a.LastNm + ', ', '') as managers
FROM User a
JOIN ManagerStaff b ON a.UserUID = b.mgruseruid
WHere a.useractive = 1
and a.UserUID in (select mgruseruid FROM ManagerStaff where staffuseruid in(1))
0
 
r3nderAuthor Commented:
This gives  me everything I need except it does not put the managers on the same line = see staffuseruid 1

userfullname	mgruseruid	mgrstaffuid	staffuseruid	managers
Test1			406	14		1		mngr2;
Test2			406	22		2		mngr1;
test1			406	14		1		mngr1;
test3			406	17		391		mngr1;
test4			406	18		385		mngr1;
Test5			406	19		395		mngr1;
test6			406	20		390		mngr1;
Test7			406	24		425		mngr1;
Test8			406	25		410		mngr1;

Open in new window




DECLARE @SQL VARCHAR(8000)
DECLARE @userID int
DECLARE @emploeeID int
If Object_ID('tempdb..#EMPLOYEE') is not null  drop table #EMPLOYEE
 Create table #EMPLOYEE (userfullname varchar(200)  , mgruseruid int,mgrstaffuid int,staffuseruid int, managers varchar(8000))
 If Object_ID('tempdb..#MANAGER') is not null  drop table #MANAGER
 Create table #MANAGER (managers varchar(8000)  ,staffuseruid int)
If Object_ID('tempdb..#BOTH') is not null  drop table #BOTH
 Create table #BOTH (userfullname varchar(200)  , mgruseruid int,mgrstaffuid int,staffuseruid int, managers varchar(8000))
 
INSERT INTO #EMPLOYEE
Select User.FirstNm + ' ' + User.LastNm as userfullname,ManagerStaff.mgruseruid,ManagerStaff.mgrstaffuid,ManagerStaff.staffuseruid,'' as managers
FROM User User
JOIN ManagerStaff ManagerStaff ON ManagerStaff.staffuseruid = User.UserUID
WHere User.useractive = 1
AND ManagerStaff.mgruseruid = 406---@userID
INSERT INTO #MANAGER
Select Coalesce(@SQL,'')+a.FirstNm + ' ' + a.LastNm + ';' as managers,b.staffuseruid
FROM User a
JOIN ManagerStaff b ON a.UserUID = b.mgruseruid
WHere a.useractive = 1
and a.UserUID in (select mgruseruid FROM ManagerStaff where staffuseruid =1)-- @emploeeID

INSERT INTO #BOTH
Select e.userfullname, e.mgruseruid,e.mgrstaffuid,e.staffuseruid,m.managers FROM #EMPLOYEE e
JOIN #MANAGER m ON e.staffuseruid = M.staffuseruid


select * FROM #BOTH
0
 
RehanYousafCommented:
Try the following script

-------------------------------------------------------------------------
DECLARE @User TABLE (					
	Useruid	INT,
	userid	VARCHAR(50),
	FirstNm	VARCHAR(50),
	LastNm	VARCHAR(50),
	emailaddr	VARCHAR(50),
	usertype INT
)
INSERT INTO @User VALUES 
	(1,	'user1',	'test1',	'last1',	'user1@email.com', 1),
	(2,	'user2',	'test2',	'last2',	'user2@email.com',	10),
	(3,	'user3',	'test3',	'last3',	'user3@email.com',	10),
	(7,	'user7',	'test7',	'last7',	'user7@email.com',	10),
	(406,	'user406',	'test406',	'last406',	'user406@email.com',	10)

-------------------------------------------------------------------------	
DECLARE @ManagerStaff TABLE (
	mgrstaffuid	INT,
	mgruseruid	INT,
	staffuseruid INT
)
INSERT INTO @ManagerStaff VALUES
	(1,	1,	391),
	(2,	1,	396),
	(3,	1,	385),
	(4,	1,	378),
	(5,	1,	406),
	(6,	1,	410),
	(7,	1,	395),
	(8,	1,	390),
	(9,	7,	4),
	(10,	7,	3),
	(11,	1,	355),
	(12,	1,	28),
	(13,	406,	410),
	(22,	406,	2),
	(14,	406,	1),
	(15,	406,	411),
	(17,	406,	391),
	(18,	406,	385),
	(19,	406,	395),
	(20,	406,	390),
	(24,	406,	425),
	(26,	7,	1)


-------------------------------------------------------------------------
SELECT 
	u.*,
	ISNULL(STUFF
	(
		(
			SELECT 
				', ' + COALESCE(a.FirstNm + ' ' + a.LastNm, '') 
			FROM 
				@User a 
				JOIN @ManagerStaff b ON b.mgruseruid = a.UserUID 
			WHERE 
				b.staffuseruid = u.Useruid
			FOR XML PATH('')
		)
		,1,2,''
	),'') as manager
FROM 
	@User u

Open in new window

0
 
r3nderAuthor Commented:
yes it work - but how do I convert to mine
0
 
r3nderAuthor Commented:
Please see attached
HELP.txt
0
 
YZlatCommented:
try something like this:

SELECT a.FirstNm + ' ' + a.LastNm as userfullname, 
SUBSTRING(  (  SELECT ( ',' + '''' + u.FirstNm + ' ' + u.LastNm + '''')  
FROM Users as u
INNER JOIN ManagerStaff as m ON u.UserUID=m.mgruseruid
WHERE u.useractive = 1 
and m.staffuseruid = 1 
ORDER BY  u.FirstNm + ' ' + u.LastNm  FOR XML PATH('') ), 2, 1000)
FROM Users as a
GROUP BY a.FirstNm + ' ' + a.LastNm

Open in new window

0
 
RehanYousafCommented:
What about this
-------------------------------------------------------------------------
DECLARE @PLMUser TABLE (					
	Useruid	INT,
	userid	VARCHAR(50),
	FirstNm	VARCHAR(50),
	LastNm	VARCHAR(50),
	emailaddr	VARCHAR(50),
	usertype INT
)
INSERT INTO @PLMUser VALUES 
	(1,	'user1',	'test1',	'last1',	'user1@email.com', 1),
	(2,	'user2',	'test2',	'last2',	'user2@email.com',	10),
	(3,	'user3',	'test3',	'last3',	'user3@email.com',	10),
	(7,	'user7',	'test7',	'last7',	'user7@email.com',	10),
	(406,	'user406',	'test406',	'last406',	'user406@email.com',	10)

-------------------------------------------------------------------------	
DECLARE @PLMManagerStaff TABLE (
	mgrstaffuid	INT,
	mgruseruid	INT,
	staffuseruid INT
)
INSERT INTO @PLMManagerStaff VALUES
	(1,	1,	391),
	(2,	1,	396),
	(3,	1,	385),
	(4,	1,	378),
	(5,	1,	406),
	(6,	1,	410),
	(7,	1,	395),
	(8,	1,	390),
	(9,	7,	4),
	(10,	7,	3),
	(11,	1,	355),
	(12,	1,	28),
	(13,	406,	410),
	(22,	406,	2),
	(14,	406,	1),
	(15,	406,	411),
	(17,	406,	391),
	(18,	406,	385),
	(19,	406,	395),
	(20,	406,	390),
	(24,	406,	425),
	(26,	7,	1)


-------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#EMPLOYEE') IS NOT NULL  DROP TABLE #EMPLOYEE
CREATE TABLE #EMPLOYEE (
	userfullname VARCHAR(200)  , 
	mgruseruid INT,
	mgrstaffuid INT,
	staffuseruid INT, 
	managers VARCHAR(8000)
) 

IF OBJECT_ID('tempdb..#MANAGER') IS NOT NULL  DROP TABLE #MANAGER
CREATE TABLE #MANAGER (
	managers VARCHAR(8000)  ,
	staffuseruid INT
) 

IF OBJECT_ID('tempdb..#BOTH') IS NOT NULL  DROP TABLE #BOTH
CREATE TABLE #BOTH (
	userfullname VARCHAR(200)  , 
	mgruseruid INT,
	mgrstaffuid INT,
	staffuseruid INT, 
	managers VARCHAR(8000)
) 

-------------------------------------------------------------------------
INSERT INTO 
	#EMPLOYEE 
SELECT 
	PLMUser.FirstNm + ' ' + PLMUser.LastNm AS userfullname,
	PLMManagerStaff.mgruseruid,
	PLMManagerStaff.mgrstaffuid,
	PLMManagerStaff.staffuseruid,
	'' AS managers 
FROM 
	@PLMUser PLMUser 
	JOIN @PLMManagerStaff PLMManagerStaff ON PLMManagerStaff.staffuseruid = PLMUser.UserUID 
WHERE 
	--PLMUser.useractive = 1 
	PLMManagerStaff.mgruseruid = 406---@userID

INSERT INTO 
	#MANAGER
SELECT 
	a.FirstNm + ' ' + a.LastNm  AS managers,
	b.staffuseruid
FROM 
	@PLMUser a 
	JOIN @PLMManagerStaff b ON a.UserUID = b.mgruseruid 
WHERE 
	--a.useractive = 1 
	a.UserUID IN (SELECT mgruseruid FROM @PLMManagerStaff WHERE staffuseruid =1)-- @emploeeID
	AND b.staffuseruid = staffuseruid

INSERT INTO 
	#BOTH
SELECT 
	e.userfullname, 
	e.mgruseruid,
	e.mgrstaffuid,
	e.staffuseruid,
	m.managers 
FROM 
	#EMPLOYEE e
	JOIN #MANAGER m ON e.staffuseruid = m.staffuseruid

-------------------------------------------------------------------------
SELECT 
	* 
FROM 
	#BOTH

SELECT 
	u.*,
	ISNULL(STUFF
	(
		(
			SELECT 
				', ' + COALESCE(b.managers, '') 
			FROM 
				#MANAGER b
			WHERE 
				b.staffuseruid = u.staffuseruid
			FOR XML PATH('')
		)
		,1,2,''
	),'') AS manager
FROM 
	#EMPLOYEE u 

Open in new window

0
 
r3nderAuthor Commented:
I created a function to add the names to a single column thank you for all your help
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now