June 13, 2012 at 2:40 pm
Trying to create a report for someone that will list out all employees for a single office along with any assignments that employee might have. I am not sure how to handle the varying number of assignments an individual might have. Also, having trouble searching for a possible solution since I'm not sure what the technical term for this kind of query is. The table structure, and example of expected results follows:
Employee
IDFirstLastextOffice
1BobBart9292MN
2JaneSway3535MN
3BillBing4444MN
4DaveTave5555MN
5LarryOdell6666MN
6JamesHoffa7777MN
7AndyGaff8888MN
8FredoPony9999OH
Assign
AtnyIDSecID
41
51
32
62
72
create table #Employee (ID int, First varchar(20), Last varchar(20), ext varchar(5), Office varchar(2))
INSERT INTO #Employee VALUES(1,Bob,Bart,9292,MN)
INSERT INTO #Employee VALUES(2,Jane,Sway,3535,MN)
INSERT INTO #Employee VALUES(3,Bill,Bing,4444,MN)
INSERT INTO #Employee VALUES(4,Dave,Tave,5555,MN)
INSERT INTO #Employee VALUES(5,Larry,Odell,6666,MN)
INSERT INTO #Employee VALUES(6,James,Hoffa,7777MN)
INSERT INTO #Employee VALUES(7,Andy,Gaff,8888,MN)
INSERT INTO #Employee VALUES(8,Fredo,Pony,9999,OH)
create table #Assign (atnyID int, secID int)
INSERT INTO #Assign VALUES(4,1)
INSERT INTO #Assign VALUES(5,1)
INSERT INTO #Assign VALUES(3,2)
INSERT INTO #Assign VALUES(6,2)
INSERT INTO #Assign VALUES(7,2)
Results (WHERE Office = 'MN')
IDFirstLastPhoneOfficeAtny1Phone1ATNY2Phone2Atny3Phone3
1BobBart9292MNTave5555Odell6666
2JaneSway3535MNBing4444Hoffa7777Gaff8888
3BillBob4444MN
4DaveTave5555MN
5LarryOdell6666MN
6JamesHoffa7777MN
7AndyGaff8888MN
June 13, 2012 at 3:13 pm
First, let me say nice work on the data setup and results, however, you didn't test it and you missed a spot. It needs quotes for the text entries, and you missed a comma. Repaired sample data below:
create table #Employee (ID int, First varchar(20), Last varchar(20), ext varchar(5), Office varchar(2))
INSERT INTO #Employee VALUES(1,'Bob','Bart',9292,'MN')
INSERT INTO #Employee VALUES(2,'Jane','Sway',3535,'MN')
INSERT INTO #Employee VALUES(3,'Bill','Bing',4444,'MN')
INSERT INTO #Employee VALUES(4,'Dave','Tave',5555,'MN')
INSERT INTO #Employee VALUES(5,'Larry','Odell',6666,'MN')
INSERT INTO #Employee VALUES(6,'James','Hoffa',7777,'MN')
INSERT INTO #Employee VALUES(7,'Andy','Gaff',8888,'MN')
INSERT INTO #Employee VALUES(8,'Fredo','Pony',9999,'OH')
create table #Assign (atnyID int, secID int)
INSERT INTO #Assign VALUES(4,1)
INSERT INTO #Assign VALUES(5,1)
INSERT INTO #Assign VALUES(3,2)
INSERT INTO #Assign VALUES(6,2)
INSERT INTO #Assign VALUES(7,2)
Now that that's fixed, looking at your results, you've combined a few different concepts into a single resultset, which is part of why you're banging your head on the wall trying to find assistance in the solution online. Not a bad thing but it can make you crazy.
The first part of what you need to do is usually a self-join. This is common in the basics of what you need to build, which is an OrgChart or BOM (Bill of Materials) join. This is because, in theory, you'll end up with multiple layers. That said, with the sample data, this is pretty easy:
So, first you need to get everything to associate correctly:
DECLARE @Office VARCHAR(2)
SET @Office = 'MN'
SELECT
Boss.ID, Boss.First, Boss.Last, Boss.Ext, Boss.Office,
Emp.Last AS EmpLast, Emp.Ext AS EmpExt
FROM
-- First, a list of everyone who can be a boss in that area
(SELECT DISTINCT
SecID AS BossID
FROM
#Assign AS a
JOIN
#Employee AS e
ONa.SecID = e.ID
WHERE
e.Office = @office
) AS drv
-- Now, get their info
JOIN
#Employee AS Boss
ONdrv.BossID = Boss.ID
-- Now find out who works for them
JOIN
#Assign AS a
ONBoss.ID = a.SecID
-- Now get the employee's info
JOIN
#Employee emp
ONa.atnyID = emp.ID
Now you'll have to pivot the data somehow. I do not recommend trying to do dynamic pivots in SQL if you're unfamiliar with the rest of standard T-SQL methodologies. It's enough to tie your hair in knots. However, because you have an unknown number of results, a Dynamic Pivot to multiple columns is all you'd really have an option to do.
However, there's an alternative. Since this data is merely for a report, you can use a trick to turn these into a single column with a separator (say, pipes - |) and list them to the end user that way.
An excellent article on what the next piece of code does: http://www.sqlservercentral.com/articles/FOR+XML+PATH/70203/
So, building on our already existing list and using that as a CTE (common table expression), you get:
EDIT: Whoops, hit post too early...
DECLARE @Office VARCHAR(2)
SET @Office = 'MN'
;WITH cte AS
(SELECT
Boss.ID, Boss.First, Boss.Last, Boss.Ext, Boss.Office,
Emp.Last AS EmpLast, Emp.Ext AS EmpExt
FROM
-- First, a list of everyone who can be a boss in that area
(SELECT DISTINCT
SecID AS BossID
FROM
#Assign AS a
JOIN
#Employee AS e
ONa.SecID = e.ID
WHERE
e.Office = @office
) AS drv
-- Now, get their info
JOIN
#Employee AS Boss
ONdrv.BossID = Boss.ID
-- Now find out who works for them
JOIN
#Assign AS a
ONBoss.ID = a.SecID
-- Now get the employee's info
JOIN
#Employee emp
ONa.atnyID = emp.ID
)
SELECT DISTINCT
ID, [First], [Last], Ext, Office,
REPLACE(( SELECT DISTINCT c2.EmpLast + ' ' + c2.EmpExt + '|' AS 'data()'
FROMcte AS c2
WHEREc2.id = cte.id
FOR XML PATH('')) + '$', '|$', '') AS AssignedEmployees
FROM
cte
I'm sure you're going to have a lot of questions. Take a look at what this gives you and then post back, we'll answer as best we can. This assumes you only want to list a single tier of hierarchy though, and if you want to go to a recursive hierarchy (where the boss lists everyone under him, including those who aren't direct reports), this gets more complicated.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 13, 2012 at 7:34 pm
My preference is the Dynamic Crosstab approach, so let me give you the briefest of tutorials on how I go about achieving a working query.
First, you need to create a query that correctly displays your result set. While it uses the DDL and data setup provided by Evil, my approach to the query is slightly different:
-- First create your Cross Tab with a known number of columns for an example to work from
;WITH Assignments AS (
SELECT e.ID, e.First, e.Last, e.Ext, e.Office
,Last1=b.Last
,ext1=b.ext
,Office1=b.Office
,n=ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY b.ID)
FROM #Employee e
LEFT JOIN #Assign a ON e.ID = a.secID
LEFT JOIN #Employee b ON a.atnyID = b.ID)
SELECT ID, First=MAX(First), Last=MAX(Last), Ext=MAX(Ext), Office=MAX(Office)
,Last1 =MAX(CASE n WHEN 1 THEN Last1 ELSE NULL END)
,Ext1 =MAX(CASE n WHEN 1 THEN Ext1 ELSE NULL END)
,Office1 =MAX(CASE n WHEN 1 THEN Office1 ELSE NULL END)
,Last2 =MAX(CASE n WHEN 2 THEN Last1 ELSE NULL END)
,Ext2 =MAX(CASE n WHEN 2 THEN Ext1 ELSE NULL END)
,Office2 =MAX(CASE n WHEN 2 THEN Office1 ELSE NULL END)
,Last3 =MAX(CASE n WHEN 3 THEN Last1 ELSE NULL END)
,Ext3 =MAX(CASE n WHEN 3 THEN Ext1 ELSE NULL END)
,Office3 =MAX(CASE n WHEN 3 THEN Office1 ELSE NULL END)
FROM Assignments
GROUP BY ID
WHERE Office = 'MN'
The SQL above is comprised of 3 parts:
1) What I call the preamble, in other words the part that is fixed and unchanging regardless of how many columns in the output set.
2) The repeating group, which starts with the line ",Last1 =" and ends with the line ",Office3 ="
3) The remaining, fixed SQL (FROM and GROUP BY)
Now lets proceed to construct the Dynamic version of this. First we need to know how many sets of columns there are:
-- Declare some variables to use in setting up the Dynamic SQL
DECLARE @SQL NVARCHAR(MAX), @Assignments INT, @i VARCHAR(2) = '1'
-- Need to know the maximum number of assignments to decide how
-- many columns to output in the result set
SELECT @Assignments=MAX(secID)
FROM (SELECT COUNT(secID) FROM #Assign GROUP BY secID) a(secID)
Next, we create the preamble SQL, which is as easy as copying down the working SQL preamble and putting quotes around it:
-- Copy down the first part of the SQL (without repeating groups)
SELECT @SQL = '
;WITH Assignments AS (
SELECT e.ID, e.First, e.Last, e.Ext, e.Office
,Last1=b.Last
,ext1=b.ext
,Office1=b.Office
,n=ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY b.ID)
FROM #Employee e
LEFT JOIN #Assign a ON e.ID = a.secID
LEFT JOIN #Employee b ON a.atnyID = b.ID)
SELECT ID, First=MAX(First), Last=MAX(Last), Ext=MAX(Ext), Office=MAX(Office)'
Fortunately the above didn't have any quoted strings in it. If it does, you need to change each quote to two single quotes. Next we create the repeating group.
-- Add the repeating groups to the SQL
WHILE @i <= @Assignments
SELECT @SQL = @SQL +
',Last' + @i + '=MAX(CASE n WHEN ' + @i + ' THEN Last1 ELSE NULL END)' +
',Ext' + @i + '=MAX(CASE n WHEN ' + @i + ' THEN Ext1 ELSE NULL END)' +
',Office' + @i + '=MAX(CASE n WHEN ' + @i + ' THEN Office1 ELSE NULL END)'
,@i = CAST(@i AS INT) + 1
Note how we incremented @i (a character string).
And then finally, add the last part (3) and SELECT our @sql string to the results pane. You can then copy it out of the results pane and execute it until it is working as expected. Finally, when everything is working you add the EXEC (@SQL):
-- Add in the last part of the SQL
SELECT @SQL = @SQL + '
FROM Assignments
GROUP BY ID
WHERE Office = ''MN'''
-- Display the SQL, copy to the execute pane and try to execute until
-- the @SQL string is working
SELECT @SQL
-- Now execute it
EXEC (@SQL)
DROP TABLE #Employee, #Assign
Note when adding the final SQL where I had to change the single quotes to two single quotes.
Edit: Forgot to include only Office = 'MN'! 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 14, 2012 at 12:34 am
I tried the Dynamic pivot but it seems that this approach is very lengthy. Still, here is the code:
--Creating Tables
Create Table Employee
(IDint,
First varchar(max),
Last varchar(max),
ext Varchar(max),
Office Char(2) )
Create Table Assign
(AtnyIDint,
SecID int )
--Inserting Sample Data
Insert Into Employee
Select 1,'Bob','Bart','9292','MN'
Union ALL
Select 2,'Jane','Sway','3535','MN'
Union ALL
Select 3,'Bill','Bing','4444','MN'
Union ALL
Select 4,'Dave','Tave','5555','MN'
Union ALL
Select 5,'Larry','Odell','6666','MN'
Union ALL
Select 6,'James','Hoffa','7777','MN'
Union ALL
Select 7,'Andy','Gaff','8888','MN'
Union ALL
Select 8,'Fredo','Pony','9999','OH'
Insert Into Assign
Select 4,1
Union ALL
Select 5,1
Union ALL
Select 3,2
Union ALL
Select 6,2
Union ALL
Select 7,2
--Static Pivot
Select y.ID, y.First, y.Last, y.Phone, y.Office, IsNULL(y.Atny1, '') As Atny1, IsNULL(z.Atny1, '') As AnyPhone1, IsNULL(y.Atny2, '') As Atny2, IsNULL(z.Atny2, '') As AtnyPhone2, IsNULL(y.Atny3, '') As Atny3, IsNULL(z.Atny3, '') As AtnyPhone3 From
(Select ID, First, Last, Phone, Office, [Atny1], [Atny2], [Atny3] From
(Select c.ID, c.First, c.Last, c.ext As Phone,c.Office, d.Last As Atny,
'Atny' + Cast(ROW_NUMBER() Over (Partition By c.Id Order By (Select NULL) ) As Varchar(2) ) As rn From
(Select * from Employee As a
Full JOIN Assign As b ON a.ID = b.SecID) As c
Full JOIN
(Select * From Employee As a
JOIN Assign As b ON a.ID = b.AtnyID) As d ON c.AtnyID = d.AtnyID Where c.Office = 'MN') As x
Pivot
(max(Atny) For rn IN ([Atny1], [Atny2], [Atny3]) ) As pvt ) As y
JOIN
(Select ID, First, Last, Phone, Office, [Atny1], [Atny2], [Atny3] From
(Select c.ID, c.First, c.Last, c.ext As Phone,c.Office, d.ext As AtnyPhone,
'Atny' + Cast(ROW_NUMBER() Over (Partition By c.Id Order By (Select NULL) ) As Varchar(2) ) As rn From
(Select * from Employee As a
Full JOIN Assign As b ON a.ID = b.SecID) As c
Full JOIN
(Select * From Employee As a
JOIN Assign As b ON a.ID = b.AtnyID) As d ON c.AtnyID = d.AtnyID ) As x
Pivot
(max(AtnyPhone) For rn IN ([Atny1], [Atny2], [Atny3]) ) As pvt) As z ON y.ID = z.ID
--Dynamic Pivot
Declare @cols varchar(max), @sql varchar(max)
Declare @temp Table(Cols varchar(max) )
Declare @temp1 Table(Cols varchar(max) )
Insert Into @temp
Select 'Atny' + Cast(ROW_NUMBER() Over (Partition By c.Id Order By (Select NULL) ) As Varchar(2) ) As rn From
(Select * from Employee As a
Full JOIN Assign As b ON a.ID = b.SecID) As c
Full JOIN
(Select * From Employee As a
JOIN Assign As b ON a.ID = b.AtnyID) As d ON c.AtnyID = d.AtnyID Where c.Office = 'MN'
Insert Into @temp1
Select Distinct * From @temp
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @temp1
Set @sql = 'Select y.ID, y.First, y.Last, y.Phone, y.Office, IsNULL(y.Atny1, '''') As Atny1, IsNULL(z.Atny1, '''') As AnyPhone1, IsNULL(y.Atny2, '''') As Atny2, IsNULL(z.Atny2, '''') As AtnyPhone2, IsNULL(y.Atny3, '''') As Atny3, IsNULL(z.Atny3, '''') As AtnyPhone3 From
(Select ID, First, Last, Phone, Office, '+@cols+' From
(Select c.ID, c.First, c.Last, c.ext As Phone,c.Office, d.Last As Atny,
''Atny'' + Cast(ROW_NUMBER() Over (Partition By c.Id Order By (Select NULL) ) As Varchar(2) ) As rn From
(Select * from Employee As a
Full JOIN Assign As b ON a.ID = b.SecID) As c
Full JOIN
(Select * From Employee As a
JOIN Assign As b ON a.ID = b.AtnyID) As d ON c.AtnyID = d.AtnyID Where c.Office = ''MN'') As x
Pivot
(max(Atny) For rn IN ('+@cols+') ) As pvt ) As y
JOIN
(Select ID, First, Last, Phone, Office, '+@cols+' From
(Select c.ID, c.First, c.Last, c.ext As Phone,c.Office, d.ext As AtnyPhone,
''Atny'' + Cast(ROW_NUMBER() Over (Partition By c.Id Order By (Select NULL) ) As Varchar(2) ) As rn From
(Select * from Employee As a
Full JOIN Assign As b ON a.ID = b.SecID) As c
Full JOIN
(Select * From Employee As a
JOIN Assign As b ON a.ID = b.AtnyID) As d ON c.AtnyID = d.AtnyID ) As x
Pivot
(max(AtnyPhone) For rn IN ('+@cols+') ) As pvt) As z ON y.ID = z.ID'
Execute (@sql)
June 14, 2012 at 7:47 am
This seems to work, Kraig. I had gotten to the point of the self join, but I had no idea about the XML PATH command to bring the results into line. I've got to add a union to the final query to list the attorneys as this query would only return the Paras who have attorneys assigned to them. I think I can get this to work with the actual data in my report very nicely, though. Now I have to read that XML PATH article. Thanks so much for your help!
June 14, 2012 at 3:05 pm
dtoews 7666 (6/14/2012)
This seems to work, Kraig. I had gotten to the point of the self join, but I had no idea about the XML PATH command to bring the results into line. I've got to add a union to the final query to list the attorneys as this query would only return the Paras who have attorneys assigned to them. I think I can get this to work with the actual data in my report very nicely, though. Now I have to read that XML PATH article. Thanks so much for your help!
My pleasure. The other approaches are also solid, but you have to be prepared to deal with variable meta-data in whatever environment you're actually presenting the data to the user with, which can get confusing. However, from a pure T-SQL approach, they're both good methods and worth your time to review eventually, even if not for this particular end result.
The XML PATH technique is really good when your front end can't pivot your data well, and you need to create lists like that. In general though, you'd prefer your front end to do things like that so IT handles the metadata confusion.
Holler back if you run into any issues. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply