Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql Query to pivot multiple rows to one row and add columns

Posted on 2012-12-30
6
Medium Priority
?
462 Views
Last Modified: 2013-02-22
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
0
Comment
Question by:DannerCorp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 13

Expert Comment

by:F Igor
ID: 38731628
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'

Open in new window

0
 

Author Comment

by:DannerCorp
ID: 38731799
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38731856
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' 

Open in new window

0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:DannerCorp
ID: 38733851
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
0
 
LVL 2

Expert Comment

by:harshada_sonawane
ID: 38734122
try this

SELECT  e.emp_id,e.name, Total_Hours ,[401K-Match] ,[401K-Trade] from

(
select emp_id,sum([401KM])+sum([401KT]) Total_Hours ,
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
0
 
LVL 8

Accepted Solution

by:
rpkhare earned 1600 total points
ID: 38734875
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:
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)

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 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