August 14, 2014 at 8:02 pm
So I've found this:
http://sqlmag.com/t-SQL/pivoting-without-aggregation
I tried to mimic this for what I need and keep getting an error.
Anyone familiar with SQL PIVOT without aggregation?
SELECT *
FROM dbo.vTeamCoaches
PIVOT (max(TeamId) FOR PersonId IN ([CoachFN], [CoachLN], [CoachEmail])) pvt[/code]
Current result:
ReginaBurtona@yahoo.com Null Null Null
RobinDillon1@gmail.com Null Null Null
KathyJanik 2@gmail.com Null Null Null
JenniferHeurlinj@hotmail.com Null Null Null
HeatherBlaskoh@comcast.net Null Null Null
The first Team has two different names in the same TeamID (not sure why it's not showing this column?), so when each TeamID has multiple person (AKA: Coach RE: MemberTypeID=2), I need to show them as a separate column which includes the FirstName, LastName, Email.
End Result sought:
TEA00001888ReginaBurtonaskateburton@yahoo.comRobinDillonrhdillon1@gmail.com
TEA00001889KathyJanik k@gmail.com Null Null Null
TEA00001890Jennifer1@hotmail.comNull Null Null
TEA00001891HeatherBlaskoh@comcast.net Null Null Null
So should show the following columns:
TeamID, FirstName, LastName, Email, (if more than one for TeamID the -->) CoachFN1, CoachLN1, CoachEmail1, CoachFN2, CoachLN2, CoachEmail2, etc ...
August 14, 2014 at 9:51 pm
August 14, 2014 at 9:55 pm
Not really understanding the difference?
Main thing with what I need is it's not an aggregate
August 14, 2014 at 10:51 pm
serviceaellis (8/14/2014)
Not really understanding the difference?Main thing with what I need is it's not an aggregate
My bad, sorry for the incomplete answer;-)
The complexity of not using an aggregate is simply to replace the group by behaviour, here is an example of a cross-tab which does that on the expense of one scan for each additional column set.
😎
USE tempdb;
GO
DECLARE @CROSSTABTBL TABLE
(
TeamID VARCHAR(20) NOT NULL
,FirstName VARCHAR(20) NOT NULL
,LastName VARCHAR(20) NOT NULL
,EmaiL VARCHAR(20) NOT NULL
,MemberTypeID INT NOT NULL
);
INSERT INTO @CROSSTABTBL
(
TeamID
,FirstName
,LastName
,MemberTypeID
)
VALUES
('TEAM001','Joe' ,'Smith','email01@some.com',1)
,('TEAM001','Chris','Jones','email02@some.com',2)
,('TEAM001','Stan' ,'Taylor','email03@some.com',3)
,('TEAM001','Ann' ,'Brown','email04@some.com',4)
,('TEAM002','Will' ,'Williams','email05@some.com',1)
,('TEAM002','Dean' ,'Wilson','email06@some.com',2)
,('TEAM002','Dan' ,'Johnson','email07@some.com',3)
,('TEAM002','Steve','Davies','email08@some.com',4)
,('TEAM003','Jane' ,'Robinson','email09@some.com',1)
,('TEAM003','Anton','Wright','email10@some.com',2)
,('TEAM003','Bob' ,'Thompson','email11@some.com',3)
,('TEAM003','Dave' ,'Evans','email12@some.com',4);
SELECT
CA.TeamID
,CA.FirstName
,CA.LastName
,CA.EmaiL
,CA.MemberTypeID
,CASE WHEN CB.MemberTypeID = 2 THEN CB.FirstName END AS C_2_FirstName
,CASE WHEN CB.MemberTypeID = 2 THEN CB.LastName END AS C_2_LastName
,CASE WHEN CB.MemberTypeID = 2 THEN CB.EmaiL END AS C_2_EmaiL
,CASE WHEN CC.MemberTypeID = 3 THEN CC.FirstName END AS C_3_FirstName
,CASE WHEN CC.MemberTypeID = 3 THEN CC.LastName END AS C_3_LastName
,CASE WHEN CC.MemberTypeID = 3 THEN CC.EmaiL END AS C_3_EmaiL
,CASE WHEN CD.MemberTypeID = 4 THEN CD.FirstName END AS C_4_FirstName
,CASE WHEN CD.MemberTypeID = 4 THEN CD.LastName END AS C_4_LastName
,CASE WHEN CD.MemberTypeID = 4 THEN CD.EmaiL END AS C_4_EmaiL
FROM @CROSSTABTBL CA
OUTER APPLY @CROSSTABTBL CB
OUTER APPLY @CROSSTABTBL CC
OUTER APPLY @CROSSTABTBL CD
WHERE CA.MemberTypeID = 1
AND CA.TeamID = CB.TeamID
AND CA.TeamID = CC.TeamID
AND CA.TeamID = CD.TeamID
AND CB.MemberTypeID = 2
AND CC.MemberTypeID = 3
AND CD.MemberTypeID = 4;
Results
TeamID FirstName LastName EmaiL MemberTypeID C_2_FirstName C_2_LastName C_2_EmaiL C_3_FirstName C_3_LastName C_3_EmaiL C_4_FirstName C_4_LastName C_4_EmaiL
-------------------- -------------------- -------------------- -------------------- ------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
TEAM001 Joe Smith email01@some.com 1 Chris Jones email02@some.com Stan Taylor email03@some.com Ann Brown email04@some.com
TEAM002 Will Williams email05@some.com 1 Dean Wilson email06@some.com Dan Johnson email07@some.com Steve Davies email08@some.com
TEAM003 Jane Robinson email09@some.com 1 Anton Wright email10@some.com Bob Thompson email11@some.com Dave Evans email12@some.com
August 15, 2014 at 8:51 am
I'm not sure what's going on with the MemberTypeID.
However it can't be assigned and used for anything else.
Those are IDs already defined and configured in SQL.
The MemberTypeID=2 is Coach records and that is only type id records I want.
August 15, 2014 at 9:15 am
If MemberTypeID 2 is all you care about, you can simply omit the lines of code from Eirikurs example that reference MemberTypeIDs you don't care about.
Is this for a report or something? If each team can have between 1:n coaches, pivoting out a dedicated column for each coach is going to get messy. I'm not sure what the scenario is where listing them out back to back is necessary. At least in their own columns. If all you need is the names concatenated on a line, there are better ways to do that.
ps. I dont know if the data you provided in your OP is real or not, but if it is, I would suggest making their names and emails anonymous.
August 15, 2014 at 9:42 am
JeeTee (8/15/2014)
If MemberTypeID 2 is all you care about, you can simply omit the lines of code from Eirikurs example that reference MemberTypeIDs you don't care about.Is this for a report or something? If each team can have between 1:n coaches, pivoting out a dedicated column for each coach is going to get messy. I'm not sure what the scenario is where listing them out back to back is necessary. At least in their own columns. If all you need is the names concatenated on a line, there are better ways to do that.
ps. I dont know if the data you provided in your OP is real or not, but if it is, I would suggest making their names and emails anonymous.
Ah ok re: omit MemberTypeId section of code provided. But it doesn't make sense to me. It appears that's relevant in the code with the CASE WHEN.
Yes this is a report for the competition events director.
She wants to see a list of all the athletes, the competitions and the teams, with the related coach(es).
So far from what I have seen, a Team could have two coaches
but I doubt more than 3.
August 15, 2014 at 10:07 am
I figured it was probably for some sort of report; non-database people are the only ones who like de-normalized reports 🙂
FWIW, whether you use a cross tab pivot such as the one mentioned earlier or one that actually uses aggregation, both are valid for what you're doing. You're correct that what you're trying to do here doesn't truly do aggregation, but there's nothing wrong with using something like a MAX() operator to satisfy the databases need for an aggregate function, even if it's just superficial.
Some argue that one method is easier to understand than another, or that there are performance differences. Personally, I rather like pivots.
Just another spin on a solution, If you don't care about whether each value has a dedicated column, you could just concatenate the name and email into a single field.
use tempdb
go
set nocount on
go
/*** SAMPLE DATA ***/
if object_id('tempdb.dbo.#staging') is not null drop table #staging
create table #staging
(
TeamID varchar(20),
CoachRID int,
fName varchar(100),
lName varchar(100),
email varchar(500),
--A mask
CatStr as 'Coach' + cast(CoachRID as varchar(30)) + ': ' + lName + ', ' + fName + ' (' + email + ')'
)
/*
--Original select based on the table you provided in OP would look something like this
--The row_number is important to have a predictable column upon which to pivot.
select
TeamID,
CoachRID = row_number() over (partition by TeamID order by LastName, FirstName),
fName = FirstName,
lName = LastName,
email = email
from dbo.vTeamCoaches where MemberTypeID = 2
*/
insert into #staging (TeamID, CoachRID, fName, lName, email)
values ('TEAM001',1,'Joe' ,'Smith','email01@some.com')
,('TEAM001',2,'Chris','Jones','email02@some.com')
,('TEAM001',3,'Stan' ,'Taylor','email03@some.com')
,('TEAM001',4,'Ann' ,'Brown','email04@some.com')
,('TEAM002',1,'Will' ,'Williams','email05@some.com')
,('TEAM002',2,'Dean' ,'Wilson','email06@some.com')
,('TEAM003',1,'Dave' ,'Evans','email12@some.com');
/*** SELECT PIVOT ***/
select
TeamID,
Coach1 = max([1]),
Coach2 = max([2]),
Coach3 = max([3]),
Coach4 = max([4])
from (select *
from #Staging) s --source
pivot(max(CatStr) for CoachRID in ([1],[2],[3],[4])) p
group by TeamID
August 15, 2014 at 10:31 am
lol, 'tis true ... report requesters
I did see the concat option, unfortunately she does not want it in one field.
August 15, 2014 at 11:36 am
serviceaellis (8/15/2014)
JeeTee (8/15/2014)
If MemberTypeID 2 is all you care about, you can simply omit the lines of code from Eirikurs example that reference MemberTypeIDs you don't care about.Is this for a report or something? If each team can have between 1:n coaches, pivoting out a dedicated column for each coach is going to get messy. I'm not sure what the scenario is where listing them out back to back is necessary. At least in their own columns. If all you need is the names concatenated on a line, there are better ways to do that.
ps. I dont know if the data you provided in your OP is real or not, but if it is, I would suggest making their names and emails anonymous.
Ah ok re: omit MemberTypeId section of code provided. But it doesn't make sense to me. It appears that's relevant in the code with the CASE WHEN.
Yes this is a report for the competition events director.
She wants to see a list of all the athletes, the competitions and the teams, with the related coach(es).
So far from what I have seen, a Team could have two coaches
but I doubt more than 3.
The MemberTypeId is used in the example as a column set directive in order to demonstrate the method used. It is not meant to be a final solution.
Obviously one cannot see what is on your screen nor access any additional knowledge on neither the requirements nor the data set further than provided on the forum.
😎
August 15, 2014 at 12:43 pm
Ok
NOTE: i provided code that's sort of working
with the data set (though just a few records)
as well as the current output.
I thought that was enough information?
1) sample data set
2) explanation at issue
3) code at issue
4) current code output
5) issue on the current code output
The list attached. The full data set
I did find the most coaches per Team is 4.
August 15, 2014 at 1:02 pm
This is not working since you are using column names in the Pivot ON:
PIVOT (max(TeamId) FOR PersonId IN ([CoachFN], [CoachLN], [CoachEmail])) pvt
should be:
PIVOT (max(TeamId) FOR PersonId IN (PER00388878, PER00500029,PER00530907, ...)) pvt
Pivot is looking for specific PersonIDs as specified in the list that follows, NOT column names.
August 15, 2014 at 1:08 pm
gbritton1 (8/15/2014)
This is not working since you are using column names in the Pivot ON:
PIVOT (max(TeamId) FOR PersonId IN ([CoachFN], [CoachLN], [CoachEmail])) pvt
should be:
PIVOT (max(TeamId) FOR PersonId IN (PER00388878, PER00500029,PER00530907, ...)) pvt
Pivot is looking for specific PersonIDs as specified in the list that follows, NOT column names.
Ok but I need to "PIVOT" the 3 fields, CoachFN, CoachLN, CoachEmail for each new Coach per TeamId (up to 4 coaches)
I tried to use the CoachFN, CoachLN, CoachEmail as the FOR but that just simply failed.
August 15, 2014 at 2:03 pm
Alright, I think (I hope) i have a query that will work for you, and it shouldn't matter if there are 2 coaches or 200. This query is kind of ugly because of all the dynamic SQL, but it's actually almost exactly like what I posted earlier using the concatenated string method. The only difference (other than the dynamic SQL) is that the concatenated string, instead of being something nice and human readable like 'Smith, John, JohnSmith@gmail.com' is instead delimited (I chose '~~' and '$$').
This way, you only have to mess with a single pivot. Once you have that single field pivoted out, you perform several string manipulation operators to slice those fields out into dedicated columns.
If you have any questions on what's going on here, just let me know and I'll happily delve into more detail, or provide a version which uses a static max number of coaches per team.
BTW, I used a subset of the data you provided in the Excel document to populate the temp table i use here. If you can do the same wiht the full data set, this should work exactly for your purposes.
set nocount on
go
/*****************
** DATA STAGING **
*****************/
if object_id('tempdb.dbo.#data') is not null drop table #data
create table #data
(
RowNum int,
TeamID varchar(100),
PersonID varchar(100),
CoachFN Varchar(100),
CoachLN varchar(100),
CoachEmail varchar(500),
)
--You dont need to build this on the fly if you already ahve one persisted.
if object_id('tempdb.dbo.#TallyTable') is not null drop table #TallyTable
create table #TallyTable
(
num int primary key clustered
)
insert into #Data (rownum, teamid, personid, coachfn, coachln, coachemail)
select '1', 'TEA00001888', 'PER00500029', 'Robin', 'Dillon', 'rhdillon1@gmail.com' union all
select '2', 'TEA00001888', 'PER00388878', 'Regina', 'Burton', 'askateburton@yahoo.com' union all
select '1', 'TEA00001889', 'PER00530907', 'Kathy', 'Janik', 'kjskate02@gmail.com' union all
select '1', 'TEA00001925', 'PER00467384', 'Alisa', 'Mitskog', 'atmccsp@yahoo.com' union all
select '1', 'TEA00001926', 'PER00397760', 'Nicole', 'Stauss', NULL union all
select '1', 'TEA00001929', 'PER00040751', 'Marguerite', 'Hiller', NULL union all
select '2', 'TEA00001929', 'PER00534294', 'Lisa', 'Storto-Featherston', 'lisasf@sbcglobal.net' union all
select '3', 'TEA00001929', 'PER00398420', 'Julianne', 'Sennese', 'jsennese@comcast.net'
/*** Determine how many columns you need to pivot (i.e. max number of coaches on a team) ***/
declare
@someIterator int = 1,
@maxRownum int,
@sql nvarchar(max),
@PivotColumns nvarchar(max),
@SelectColumns nvarchar(max),
@SplitColumns nvarchar(max)
select @maxRownum = max(rownum)
from #data
-- Populate a tally table
-- Jeff moden, dont yell at me :)
while @someIterator <= @maxrownum
begin
insert into #tallyTable(num)
select @someIterator
select @someIterator += 1
end
--Variables which equate to the string splitting operations, the pivot, and select columns.
select
@PivotColumns = (select quotename(num) + ','
from #tallyTable
for xml path ('')),
@SelectColumns = (select 'Coach' + cast(num as varchar(10)) + 'CatStr = max(' + quotename(num) + '),'
from #tallyTable
for xml path ('')),
@SplitColumns = (select
'Coach' + cast(num as varchar(10)) + 'FN = nullif(left(Coach' + cast(num as varchar(10)) + 'CatStr, patindex(''%~~%'', Coach' + cast(num as varchar(10)) + 'CatStr) - 1), ''''),'
+ 'Coach' + cast(num as varchar(10)) + 'LN = nullif(substring(Coach' + cast(num as varchar(10)) + 'CatStr, patindex(''%~~%'', Coach' + cast(num as varchar(10)) + 'CatStr) + 2, patindex(''%$$%'', Coach' + cast(num as varchar(10)) + 'CatStr) - patindex(''%~~%'', Coach' + cast(num as varchar(10)) + 'CatStr) - 2), ''''),'
+ 'Coach' + cast(num as varchar(10)) + 'Email = nullif(right(Coach' + cast(num as varchar(10)) + 'CatStr, len(Coach' + cast(num as varchar(10)) + 'CatStr) - patindex(''%$$%'', Coach' + cast(num as varchar(10)) + 'CatStr) -1), ''''),'
from #tallyTable
for xml path ('')),
--Chop off trailing commas
@SplitColumns = left(@SplitColumns, len(@SplitColumns) - 1),
@SelectColumns = left(@SelectColumns, len(@SelectColumns) - 1),
@PivotColumns = left(@PivotColumns, len(@PivotColumns) - 1)
--Deubgging
print @splitColumns
--Pivot on the CatStr field. Put that into a CDE so its easier to work with.
select
@sql = '
;with cte as
(
select
TeamID,
' + @SelectColumns + '
from (select
rownum,
teamid,
personid,
CatStr = isnull(coachfn, '''') + ''~~'' + isnull(coachln, '''') + ''$$'' + isnull(coachemail, ''''),
coachln,
coachemail
from #data) src
pivot (max(catstr) for rownum in (' + @pivotColumns + ')) p
group by TeamID
)
select
TeamID = TeamID, ' + @SplitColumns + '
from cte'
print @sql
exec (@sql)
August 15, 2014 at 2:17 pm
Here it is without dynamic sql (use the same data population header)
;with cte as
(
select
TeamID,
CatStr1 = max([1]),
CatStr2 = max([2]),
CatStr3 = max([3])
from (select
rownum,
teamid,
personid,
catstr = isnull(coachfn, '') + '~~' + isnull(coachln, '') + '$$' + isnull(coachemail, '')
from #data) s --source
pivot (max(catstr) for rownum in ([1], [2], [3])) p
group by TeamID
)
select
TeamID,
Coach1FN = nullif(left(CatStr1, patindex('%~~%', CatStr1) - 1), ''),
Coach1LN = nullif(substring(CatStr1, patindex('%~~%', CatStr1) + 2, patindex('%$$%', CatStr1) - patindex('%~~%', CatStr1) - 2), ''),
Coach1Email = nullif(right(CatStr1, len(CatStr1) - patindex('%$$%', CatStr1) -1), ''),
Coach2FN = nullif(left(CatStr2, patindex('%~~%', CatStr2) - 1), ''),
Coach2LN = nullif(substring(CatStr2, patindex('%~~%', CatStr2) + 2, patindex('%$$%', CatStr2) - patindex('%~~%', CatStr2) - 2), ''),
Coach2Email = nullif(right(CatStr2, len(CatStr2) - patindex('%$$%', CatStr2) -1), ''),
Coach3FN = nullif(left(CatStr3, patindex('%~~%', CatStr3) - 1), ''),
Coach3LN = nullif(substring(CatStr3, patindex('%~~%', CatStr3) + 2, patindex('%$$%', CatStr3) - patindex('%~~%', CatStr3) - 2), ''),
Coach3Email = nullif(right(CatStr3, len(CatStr3) - patindex('%$$%', CatStr3) -1), '')
from cte
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply