Solved

multiple values 1 column and combining 2 results

Posted on 2013-01-24
16
260 Views
Last Modified: 2013-01-25
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
Comment
Question by:r3nder
16 Comments
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38814590
Which SQL Server you are using?

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

it will help
0
 
LVL 6

Author Comment

by:r3nder
ID: 38814655
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
 
LVL 6

Author Comment

by:r3nder
ID: 38814743
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
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38815094
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
 
LVL 6

Author Comment

by:r3nder
ID: 38815167
2008 r2 - Please help
Book1.xlsx
0
 
LVL 31

Expert Comment

by:awking00
ID: 38815241
Given just the values for the managerstaff and user tables in your spreadsheet, Book1.xlsx, what do you want to see for results?
0
 
LVL 6

Author Comment

by:r3nder
ID: 38815290
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
 
LVL 6

Author Comment

by:r3nder
ID: 38815512
I am trying to return this for a dataset to use in a grid
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 6

Author Comment

by:r3nder
ID: 38815558
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
 
LVL 6

Author Comment

by:r3nder
ID: 38815878
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
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38815896
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
 
LVL 6

Author Comment

by:r3nder
ID: 38816081
yes it work - but how do I convert to mine
0
 
LVL 6

Author Comment

by:r3nder
ID: 38816303
Please see attached
HELP.txt
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38818734
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
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 500 total points
ID: 38818810
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
 
LVL 6

Author Comment

by:r3nder
ID: 38818919
I created a function to add the names to a single column thank you for all your help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now