Dynamic Column
-
I am trying to figure out how to take the following and dynamically generate a new column for every person and split that has the same ID so that it reads across vs up and down. No matter what I try I can't get the output the way I need it.
ID Person Split
1002878-1 Sam 8.00
1002878-1 Steve 84.00
1002878-1 Timothy 8.00
1007846-4 Bob 10.00
1007846-4 Scott 90.00ID P1 S1 P2 S2 P3 S3
1002878-1 Sam 8.00 Steve 84.00 Timothy 8.00
1007846-4 Bob 10.00 Scott 90.00 -
What have you tried? Did you try using PIVOT?
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. -
Ok I did a pivot, but for some reason my PercentageSplit value is not pulling through...can anyone see what I am missing? So here is the process that I am using:
-- Create table for query
DECLARE @Allocations TABLE
(
EngagementID VARCHAR(Max),
Person VARCHAR(Max),
PercentageSplit INT
);-- Insert data
INSERT INTO @Allocations
SELECT myFields
FROM myDataBase
LEFT OUTER JOIN myTable1 dso
ON dso.fld= sc.fld
LEFT OUTER JOIN myDataTable2 dp
ON dp.ProjectKey = dso.ProjectKey
LEFT OUTER JOIN myTable3 p
ON p.personid = sc.crnr_userid
LEFT OUTER JOIN myTable4 scs
ON scs.fld= sc.fld
LEFT OUTER JOIN nonsecure.vDimSalesCreditStatusCode scstat
ON scstat.fld= sc.fld
LEFT OUTER JOIN myTable5 wpsc
ON dso.fld= wpsc.fld
WHERE 1 = 1
AND dso.IsSalesCreditApproved = 1
AND wpsc.WorkPlanStatusCode IN (2, 316490002)
AND Isnull(crnr_effortpercent, -1) <> -1
AND dso.IsExpired = 0
GROUP BY dp.fld
,sc.fld
,sc.fld
,dso.fld
,sc.fld;WITH Allocations
AS
(SELECT
Row_number() OVER (PARTITION BY EngagementID ORDER BY EngagementID) AS RowID
,*
FROM @Allocations)
SELECT
EngagementID
,SUM(Pct1) AS Pct1
,Min(Person1) AS Person1
,Min(Pct2) AS Pct2
,Min(Person2) AS Person2
,Min(Pct3) AS Pct3
,Min(Person3) AS Person3
,Min(Pct4) AS Pct4
,Min(Person4) AS Person4
,Min(Pct5) AS Pct5
,Min(Person5) AS Person5
,Min(Pct6) AS Pct6
,Min(Person6) AS Person6
,Min(Pct7) AS Pct7
,Min(Person7) AS Person7
,Min(Pct8) AS Pct8
,Min(Person8) AS Person8
,Min(Pct9) AS Pct9
,Min(Person9) AS Person9
,Min(Pct10) AS Pct10
,Min(Person10) AS Person10
FROM (SELECT
Row_number() OVER (PARTITION BY EngagementID ORDER BY EngagementID) AS RowID
,EngagementID
,'Person' + Cast(RowID AS VARCHAR) AS PersonName
,'Percentage' + Cast(RowID AS VARCHAR) AS PctAllocated
,Person
,PercentageSplit
FROM Allocations) AS PvtPIVOT (MIN(Person)
FOR PersonName IN ([Person1], [Person2], [Person3], [Person4], [Person5], [Person6], [Person7], [Person8], [Person9], [Person10])) AS Pvt1PIVOT (MIN(PercentageSplit)
FOR PctAllocated IN ([Pct1], [Pct2], [Pct3], [Pct4], [Pct5], [Pct6], [Pct7], [Pct8], [Pct9], [Pct10])) AS Pvt2
GROUP BY EngagementID;Output result:
Names are displaying properly, but the percentages are not pulling in
The way the table looks that it is getting the data from
-
I would think you would only need one pivot statement. Pivot the name and percentage in the same statement. If you can't get that to work, I think 'Cross Apply' could be used. Do you have a fixed number or persons for each EngagementID? Is there a max? You only show 10 in your code.
Sorry, I don't have time to go through the code right now.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. -
10 is the max, at this point, of the number of people associated to an EngagementID. Of course that could change down the road. I will try your suggestion.
Thanks!
- Cheryl McLaughlin-385812 - Wednesday, March 21, 2018 1:48 PM
If 10 is the max then you can use this hardcode cross tab query mentioned below :
create TABLE Allocations
(
EngagementID VARCHAR(Max),
Person VARCHAR(Max),
PercentageSplit INT
);insert into Allocations values ('1002878-1','Sam',8.00);
insert into Allocations values ('1002878-1','Ram',80.00);
insert into Allocations values ('1002878-1','Adam',92.00);
insert into Allocations values ('1007846-2','Bob',2.00);
select engagementid,
case when rnk=1 then PercentageSplit end as S1,
case when rnk=2 then PercentageSplit end as S2,
case when rnk=3 then PercentageSplit end as S3,
case when rnk=1 then Person end as P1,
case when rnk=2 then Person end as P2,
case when rnk=3 then Person end as P3
from(select pvt.EngagementID,Sam,Ram,Adam,Bob,Person,PercentageSplit,
rank() over(partition by pvt.EngagementID
order by pvt.EngagementID,b.person) as rnkfrom (select EngagementID,Person,PercentageSplit
from Allocations)a
pivot
(
max(PercentageSplit)
for person in (Sam ,Ram,Adam,Bob)
) as pvt
inner join
Allocations b
on pvt.EngagementID=b.EngagementID
)chttp://sqlfiddle.com/#!18/a8f2b/18
Cheers,
SaravananSaravanan
-
Saravanan
Thanks for the suggestion. I like what you did, but the output is not in the layout that I need. I ended up using my pivot example and whittled it down at the suggestion made by SSCarpal Tunnel. Due to the fact that the names will always change as they work new projects I needed this as dynamic as possible.
I want to thank everyone for their input. Definitely helps when you can's see the forest through the trees. As an only developer here I do not have a sounding board when I need it. So, again, thank you everyone. Everyone's input helped with my thinking process and helped turn the light on, so to speak, lol.
Cheryl
-
OK, I couldn't figure out how to do it with PIVOT without hard coding the names. I'm sure there is a way, just not seeing it right now.
Here is what I came up with: (I would be there is a better way)
DECLARE @tbl TABLE
(
ID VARCHAR(10),
PERSON VARCHAR(10),
SPLIT DECIMAL(6,2)
)
;
INSERT INTO @tbl
(ID, Person, Split)
SELECT '1002878-1', 'Sam', 8.00
UNION ALL
SELECT '1002878-1', 'Steve', 84.00
UNION ALL
SELECT '1002878-1', 'Timothy', 8.00
UNION ALL
SELECT '1007846-4', 'Bob', 10.00
UNION ALL
SELECT '1007846-4', 'Scott', 90.00
;WITH SUBQ AS
(SELECT ID, Person, Split,
RANK() OVER(PARTITION BY ID ORDER BY Person) AS Rnk
FROM @tbl
)
,
SUBQ1 AS
(
SELECT ID, Person AS P1, Split AS S1
FROM SUBQ
WHERE Rnk = 1
)
,
SUBQ2 AS
(
SELECT ID, Person AS P2, Split AS S2
FROM SUBQ
WHERE Rnk = 2
)
,
SUBQ3 AS
(
SELECT ID, Person AS P3, Split AS S3
FROM SUBQ
WHERE Rnk = 3
)
SELECT SUBQ1.ID,
SUBQ1.P1, SUBQ1.S1,
SUBQ2.P2, SUBQ2.S2,
SUBQ3.P3, SUBQ3.S3
FROM SUBQ1
LEFT JOIN SUBQ2
ON SUBQ1.ID = SUBQ2.ID
LEFT JOIN SUBQ3
ON SUBQ1.ID = SUBQ3.ID
;-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. - Cheryl McLaughlin-385812 - Thursday, March 22, 2018 9:00 AM
Please post the SQL you used.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. -
SSCarpal Tunnel
Although, yours has the same desired layout on the output, my example allowed for the dynamic column I need on person and I didn't have to worry about hard-coding names. That needed to be dynamic. I just had to tweak a little for the percentage value. However, if in the future, there can be more than 10 people on a project I will then revisit this, but due to time crunch this is now working properly. If time ever allows, lol, I may take a closer look and see if I can find a better way than "hard-coding" for only 10 people. I have some ideas, but just don't have the time to play with code.
Again, thanks!
- Cheryl McLaughlin-385812 - Thursday, March 22, 2018 10:29 AM
If you could post the code you came up with to get the desired results it would help me, and others in the future who may have a similar issue. And by posting your code you may get someone to take a look at it and be able to show us all 'a better way'.
Below86 is my 'handle', 'SSCarpal Tunnel' is my level.-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply