Combining multiple rows into one row per employee

  • 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

  • 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.
    General Disclaimer:
    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • 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,

  • 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.
    General Disclaimer:
    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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;

  • 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.
    General Disclaimer:
    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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