How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.
Sql Query to pivot multiple rows to one row and add columns
I have a table that looks like this (there are about 140 employee ids)
EmpID | EarnID | Current_Amount | Current_Hours
1136 401KM 25.49 16
1136 401KT 118.97 16
1151 401KM 10.00 8
1151 401KT 15.00 8
200 401KM 13.00 2
200 401KT 0 2
i would like it to look like this. . .
EmpID | Current_Hours | 401KM | 401KT
1136 16 25.49 118.97
1151 8 10.00 15.00
200 2 13.00 0.00
I have tried a few different queries with no luck. . .
Hoping someone can help.
TIA,
Faunna
EmpID | EarnID | Current_Amount | Current_Hours
1136 401KM 25.49 16
1136 401KT 118.97 16
1151 401KM 10.00 8
1151 401KT 15.00 8
200 401KM 13.00 2
200 401KT 0 2
i would like it to look like this. . .
EmpID | Current_Hours | 401KM | 401KT
1136 16 25.49 118.97
1151 8 10.00 15.00
200 2 13.00 0.00
I have tried a few different queries with no luck. . .
Hoping someone can help.
TIA,
Faunna
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The following query expands the original table with separated data, and labeled using the EarnID value.
SELECT
t1.empid, t1.current_hours, t1.current_amount as 401KM, t2.current_amount as 401KT
FROM Table1 t1
LEFT JOIN Table1 t2
ON t1.emp_id=t2.emp_id AND t2.EarnID='401KT'
WHERE
t1.EarnID='401KM'
Thank you. I think we almost got it. . but there is an error:
Incorrect syntax near '401' says it cannot be bound.
Here is the query with the table name. sorry, I missed that at first.
401KM is 401k-Match and 401KT is 401k-Trade. I shortened it in the first email.
SELECT
t1.EMP_ID, t1.CURRENT_HOURS, t1.CURRENT_AMOUNT as 401K-MATCH, t2.CURRENT_AMOUNT as 401K-TRAD
FROM PROC_EMP_PAYDETAIL t1
LEFT JOIN PROC_EMP_PAYDETAIL t2
ON t1.EMP_ID=t2.EMP_ID AND t2.EARN_ID='401K-TRAD'
WHERE
t1.EARN_ID='401K-MATCH'
TIA again.
Incorrect syntax near '401' says it cannot be bound.
Here is the query with the table name. sorry, I missed that at first.
401KM is 401k-Match and 401KT is 401k-Trade. I shortened it in the first email.
SELECT
t1.EMP_ID, t1.CURRENT_HOURS, t1.CURRENT_AMOUNT as 401K-MATCH, t2.CURRENT_AMOUNT as 401K-TRAD
FROM PROC_EMP_PAYDETAIL t1
LEFT JOIN PROC_EMP_PAYDETAIL t2
ON t1.EMP_ID=t2.EMP_ID AND t2.EARN_ID='401K-TRAD'
WHERE
t1.EARN_ID='401K-MATCH'
TIA again.
You are missing square brackets around 401K-MATCH and 401K-TRAD as in (no points please):
SELECT t1.EMP_ID,
t1.CURRENT_HOURS,
t1.CURRENT_AMOUNT AS [401K-MATCH],
t2.CURRENT_AMOUNT AS [401K-TRAD]
FROM PROC_EMP_PAYDETAIL t1
LEFT JOIN PROC_EMP_PAYDETAIL t2 ON t1.EMP_ID = t2.EMP_ID
AND t2.EARN_ID = '401K-TRAD'
WHERE t1.EARN_ID = '401K-MATCH'
ok thank you : ) Before I saw your email, I figured out to put MAtch401k and Match401kTrade and that worked too.
There are two more pieces to the query that I'm wondering if you could help me with. . .
I'm sorry, I'm just learning SQL to the extent of linking tables . . .I know access like the back of my hand, but we are using SQL at my new company and I'm learning on the fly.
1.) Current_Hours is coming up blank because it is grabbing the line with the 401K-MATCH or 401K-TRADE. . This should really be a sum of all hours for that employee. (another line item in the table.) Current Hours + Current_OT_Hours - see below
EmpID | EarnID | Current_Amount | Current_Hours |Current_OT_HOURS
1136 401KM 25.49 0 0
1136 401KT 118.97 0 0
1136 FLOOR 9.14 80 22
Would this be a third table reference t3?
So it would look like this . .
EmpID | Total_Hours | 401K-Match | 401K-Trade
11 102 25.49 118.97
2) And last but not least. . .The Employee needs his personal information from another table EMPLOEE
I need to link Employee table to t1? t1.EMPID = Employee.EMPID and grab LASTNAME . .
All on one line like this. . .
EmpID | EmpLastName| Total_Hours | 401K-Match | 401K-Trade
1136 Ash 102 25.49 118.97
There are two more pieces to the query that I'm wondering if you could help me with. . .
I'm sorry, I'm just learning SQL to the extent of linking tables . . .I know access like the back of my hand, but we are using SQL at my new company and I'm learning on the fly.
1.) Current_Hours is coming up blank because it is grabbing the line with the 401K-MATCH or 401K-TRADE. . This should really be a sum of all hours for that employee. (another line item in the table.) Current Hours + Current_OT_Hours - see below
EmpID | EarnID | Current_Amount | Current_Hours |Current_OT_HOURS
1136 401KM 25.49 0 0
1136 401KT 118.97 0 0
1136 FLOOR 9.14 80 22
Would this be a third table reference t3?
So it would look like this . .
EmpID | Total_Hours | 401K-Match | 401K-Trade
11 102 25.49 118.97
2) And last but not least. . .The Employee needs his personal information from another table EMPLOEE
I need to link Employee table to t1? t1.EMPID = Employee.EMPID and grab LASTNAME . .
All on one line like this. . .
EmpID | EmpLastName| Total_Hours | 401K-Match | 401K-Trade
1136 Ash 102 25.49 118.97
try this
SELECT e.emp_id,e.name, Total_Hours ,[401K-Match] ,[401K-Trade] from
(
select emp_id,sum([401KM])+sum([4
sum([401KM]) as [401K-Match] ,sum([401KT]) as [401K-Trade]
from
(select * FROM emp_det)AS mytable
pivot
(
sum(Current_hours) FOR earnid IN ([401KM],[401KT])
) as pvt GROUP BY pvt.emp_id
) p
INNER JOIN EMPLOEE e ON e.emp_id=p.emp_id
SELECT e.emp_id,e.name, Total_Hours ,[401K-Match] ,[401K-Trade] from
(
select emp_id,sum([401KM])+sum([4
sum([401KM]) as [401K-Match] ,sum([401KT]) as [401K-Trade]
from
(select * FROM emp_det)AS mytable
pivot
(
sum(Current_hours) FOR earnid IN ([401KM],[401KT])
) as pvt GROUP BY pvt.emp_id
) p
INNER JOIN EMPLOEE e ON e.emp_id=p.emp_id
The above suggested solutions work when you desire only two fixed columns i.e., 401KM and 401KT.
Just in case your table grows and you have new values in EarnID column and you want your query to automatically handle those new values as columns, try this dynamic query:
Just in case your table grows and you have new values in EarnID column and you want your query to automatically handle those new values as columns, try this dynamic query:
IF OBJECT_ID('tempdb..#Results',N'U') IS NOT NULL
DROP TABLE #Results
select empid,earnid,current_amount,current_hours into #Results from <your_table_name>
declare @cols nvarchar(max)
declare @sql1 nvarchar(max)
select @Cols = stuff((select ', ' + quotename(EarnId)
from (select distinct top 100 earnid
as EarnId from #Results ORDER BY earnid) X
ORDER BY EarnId FOR XML PATH('')),1,2,'')
set @sql1 = 'SELECT * FROM #Results PIVOT (max(earnid) FOR earnid IN (' + @Cols +'))pvt'
execute (@sql1)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial