Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Combining multiple rows into one row per employee Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 5:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
Hello Everyone,

I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.

If someone kindly help I will be grateful.

Thank you,


Sample Data:

WITH SampleData (PERSON, [DATA], [FIELD]) AS
(
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,80.0,'Rate'

)
SELECT *
FROM SampleData;

The results from the above are as follows:

PERSON	DATA	        FIELD
1234 04/02/2014 Date
1234 123 Department
1234 80.0 Rate

The desired results would be:

PERSON	Date	    Department  Rate
1234 04/02/2014 123 80.0

Post #1558313
Posted Thursday, April 3, 2014 5:27 PM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 3,556, Visits: 7,671
Check the following article on Cross Tabs: http://www.sqlservercentral.com/articles/T-SQL/63681/

WITH SampleData (PERSON, [DATA], [FIELD]) AS
(
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,'80.0','Rate'

)
SELECT PERSON,
MAX( CASE WHEN FIELD = 'Date' THEN DATA END) AS [Date],
MAX( CASE WHEN FIELD = 'Department' THEN DATA END) AS Department,
MAX( CASE WHEN FIELD = 'Rate' THEN DATA END) AS Rate
FROM SampleData
GROUP BY PERSON;




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1558318
Posted Thursday, April 3, 2014 5:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
Hi Guys,

I was able to get it done this way, not sure if this is most effective way though so please if you have a better solution please do let me know.

Thank you,


Select DISTINCT PERSONNUM,
(
SELECT max(DATA)
FROM SampleData
WHERE (h.PERSONNUM = PERSONNUM) AND (FIELD = 'Date')
) CONTRACT_DATE,
(
SELECT max(DATA)
FROM SampleData
WHERE (h.PERSONNUM = PERSONNUM) AND (FIELD = 'Department')
) SUMMER_DEPT,
(
SELECT max(DATA)
FROM SampleData
WHERE (h.PERSONNUM = PERSONNUM) AND (FIELD = 'Rate')
) SUMMER_RATE
FROM SampleData H

Post #1558319
Posted Thursday, April 3, 2014 5:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
Hi Luis,

I just saw your reply, thank you very much for such a quick reply.

I will try your code and I will let you know how it comes out.

Thank you again,
Post #1558320
Posted Thursday, April 3, 2014 5:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 3,556, Visits: 7,671
That's a horrible way to do it. You're reading the table 4 times instead of one.
I gave you an example and an article that fully explains that method.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1558321
Posted Thursday, April 3, 2014 6:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
Hi Luis,

This is great!!

Would you kindly tell me how do I use my table in the following query?

My table already has data and the table is as follows:

CREATE TABLE TempSSProf_CustData
(PERSON [nvarchar](15) NOT NULL,
DATA [nvarchar](200) NULL,
FIELD [nvarchar](30) NOT NULL)


Thank you Luis.


WITH SampleData (PERSON, [DATA], [FIELD]) AS -- I will replace SampleData with this table TempSSProf_CustData
(
/**what goes in here?**/
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,'80.0','Rate'

)
SELECT PERSON,
MAX( CASE WHEN FIELD = 'Date' THEN DATA END) AS [Date],
MAX( CASE WHEN FIELD = 'Department' THEN DATA END) AS Department,
MAX( CASE WHEN FIELD = 'Rate' THEN DATA END) AS Rate
FROM SampleData -- I will replace SampleData with this table TempSSProf_CustData
GROUP BY PERSON;

Post #1558326
Posted Thursday, April 3, 2014 6:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 3,556, Visits: 7,671
What's your problem? You just need to remove the CTE and replace it with your table. You've mentioned that on the comments, so I can't see the problem.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1558327
Posted Friday, April 4, 2014 12:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
Hi Luis,

Sorry about the confusion and stupid question

Was doing too many things at the same time and obviously not thinking straight, everything is working great, thank you once again for all the help.

God bless..
Post #1558378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse