April 3, 2014 at 5:04 pm
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:
PERSONDATA FIELD
123404/02/2014Date
1234123 Department
123480.0 Rate
The desired results would be:
PERSONDate Department Rate
123404/02/2014 123 80.0
April 3, 2014 at 5:27 pm
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;
April 3, 2014 at 5:34 pm
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
April 3, 2014 at 5:36 pm
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,
April 3, 2014 at 5:37 pm
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.
April 3, 2014 at 6:08 pm
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;
April 3, 2014 at 6:11 pm
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.
April 4, 2014 at 12:27 am
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..
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply