How to combine 3 rows into one

  • Hi,

    I have a query that returns data like this:

    Name emp id TypeCode TValue TypeCode TValue TypeCode Value

    Don King 1234 S 8

    Don King 1234 V 8

    Don King 1234 H 8

    This is for a employee that for a 2 week pay period has 8 hours vacation, 8 hours sick time and 8 hours holiday. These are in 3 different records in the database.

    What I would like is for the below result where they are all on one line.

    Name emp id TypeCode TValue TypeCode TValue TypeCode Value

    Don King 1234 S 8 V 8 H 8

    Thanks for any help in the right direction.

    Dave

  • Take a look at the links in my signature about cross tabs. It explains how to do this thing quite nicely.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I am already doing a case statement to get to where Im at right now for the columns. The original data is in one column so I have split it into multiple columns, but dont know how to go from there.

    Thanks

  • In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Here are the create table statements:

    CREATE TABLE #tblTimeOffRequest

    (

    [EmpId] [int] NULL,

    [Type] [varchar](40) NULL,

    [From_Date] [smalldatetime] NULL,

    [To_Date] [smalldatetime] NULL,

    [Total_Hours] [decimal](5, 2) NULL,

    [Status] [varchar](20) DEFAULT ('Pending'),

    )

    INSERT INTO #tblTimeOffRequest

    (EmpID, Type, From_Date, To_Date, Total_Hours, Status)

    SELECT '500','Sick','Oct 24 2013 12:00AM','Oct 24 2013 12:00AM','4.00','Approved' UNION ALL

    SELECT '500','Sick','Oct 25 2013 12:00AM','Oct 25 2013 12:00AM','8.00','Approved' UNION ALL

    SELECT '500','Vacation','Oct 21 2013 12:00AM','Oct 22 2013 12:00AM','16.00','Pending' UNION ALL

    SELECT '125','Vacation','Oct 21 2013 12:00AM','Oct 21 2013 12:00AM','8.00','Approved' UNION ALL

    SELECT '125','Sick','Oct 14 2013 12:00AM','Oct 14 2013 12:00AM','8.00','Approved' UNION ALL

    SELECT '110','Pay In Leiu','Oct 23 2013 12:00AM','Oct 25 2013 12:00AM','24.00','Approved' UNION ALL

    SELECT '110','Sick','Oct 16 2013 12:00AM','Oct 16 2013 12:00AM','8.00','Pending'

    -----------------------------------

    CREATE TABLE #tblEmployee

    (

    [EmpId] [int] IDENTITY(1,1) NOT NULL,

    [EmpNum] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [EmpFirstName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [EmpLastName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Active] [bit] NULL CONSTRAINT [DF_tblEmployee_Active] DEFAULT (1),

    [Location] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [PayType] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    )

    SET IDENTITY_INSERT #tblEmployee ON

    INSERT INTO #tblEmployee

    (EmpID, EmpNum, EmpFirstName, EmpLastName, Active, Location, PayType)

    SELECT '500','2380','Don','King','1','PEI','S' UNION ALL

    SELECT '125','21038','Jam','Jahana','1','PEI','S' UNION ALL

    SELECT '110','21010','Bill','Smith','1','PEI','S' UNION ALL

    SELECT '27','2019','Edward','Dicks','1','PEI','S' UNION ALL

    SELECT '28','2143','Rowdy','Bergross','1','PEI','S' UNION ALL

    SELECT '29','16164','Corinna','Tillstoy','1','CML','S' UNION ALL

    SELECT '30','2186','Paula','Pie','1','PEI','S'

    -----------------------------------------------------------------

    This is the query im running:

    declare @FromDate as smalldatetime

    declare @Todate as smalldatetime

    declare @Loc as varchar(25)

    set @Fromdate = '10/13/2013'

    SET @ToDate = '10/26/2013'

    SET @Loc = 'PEI'

    SELECT

    B.Location,

    'EXEMPT' as 'Batch ID',

    A.EmpId as 'File #',

    B.EmpLastName as 'Last Name',

    B.EmpFirstName as 'First Name',

    'y' as 'Cancel Pay',

    NULL as 'Pay #',

    NULL as 'Reg Earnings',

    2 AS 'Rate Code',

    NULL as 'Tax Frequency',

    NULL as 'Reg Hours',

    CASE WHEN Type = 'Sick' THEN 'S' end as 'Hours 4 Code',

    SUM(CASE WHEN Type = 'Sick' THEN A.Total_Hours end) as 'Hours 4 Amount',

    CASE WHEN Type = 'Vacation' THEN 'V' end as 'Hours 4 Code',

    SUM(CASE WHEN Type = 'Vacation' THEN A.Total_Hours end) as 'Hours 4 Amount',

    CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN 'O' end as 'Hours 4 Code',

    SUM(CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN A.Total_Hours end) as 'Hours 4 Amount'

    FROM #tblTimeOffRequest A

    INNER Join

    #tblEmployee B

    ON

    A.EmpId = B.EmpId

    WHERE From_Date between @FromDate AND @ToDate

    AND

    Location = @Loc

    AND

    PayType = 'S'

    GROUP BY Location,A.EmpId,EmpLastName,EmpFirstName,A.Type,A.Total_Hours

    ----------------------------------------------------------------

    I am getting output like this:

    Co Code BatchId File# LastName First Cancel Pay Pay# Reg Earn RateCode Tax Freq Reg hrs Hrs4Code hrs4Amthrs4Code hrs4Amt hrs4codehrs4Amt

    K00EXEMPT500KingDonyNULLNULL2NULLNULLS 4.00NULL NULLNULLNULL

    K00EXEMPT500KingDonyNULLNULL2NULLNULLS8.00NULLNULLNULLNULL

    K00EXEMPT500KingDonyNULLNULL2NULLNULLNULLNULL V16.00NULLNULL

    I want the 3 lines above to be on 1 line with summing if more than 1 of same type. like if 2 sick days, add together.

    Let me know if you need anything else.

    Thanks

  • Something like this should do it.

    SELECT

    B.Location,

    'EXEMPT' as 'Batch ID',

    A.EmpId as 'File #',

    B.EmpLastName as 'Last Name',

    B.EmpFirstName as 'First Name',

    'y' as 'Cancel Pay',

    NULL as 'Pay #',

    NULL as 'Reg Earnings',

    2 AS 'Rate Code',

    NULL as 'Tax Frequency',

    NULL as 'Reg Hours',

    case when SUM(CASE WHEN Type = 'Sick' THEN A.Total_Hours end) IS not null then 'S' end as 'Hours 4 Code',

    SUM(CASE WHEN Type = 'Sick' THEN A.Total_Hours end) as 'Sick Hours',

    case when SUM(CASE WHEN Type = 'Vacation' THEN A.Total_Hours end) IS not null then 'V' end as 'Hours 4 Code',

    SUM(CASE WHEN Type = 'Vacation' THEN A.Total_Hours end) as 'Vacation Hours',

    case when SUM(CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN A.Total_Hours end) is not null then 'O' end as 'Hours 4 Code',

    SUM(CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN A.Total_Hours end) as 'Unknown Hours'

    FROM #tblTimeOffRequest A

    INNER Join #tblEmployee B ON A.EmpId = B.EmpId

    WHERE From_Date between @FromDate AND @ToDate

    AND Location = @Loc

    AND PayType = 'S'

    GROUP BY Location, A.EmpId, EmpLastName, EmpFirstName

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Im still getting the same output with the new code?

  • I am not sure what the "new code" added means. This returns exactly same set as Sean but using PIVOT. Just a different approach. I compared the two in Actual Query Execution plan and Sean's had a lower query cost.

    SELECT

    pvt.Location,

    'EXEMPT' as 'Batch ID',

    pvt.EmpId as 'File #',

    pvt.EmpLastName as 'Last Name',

    pvt.EmpFirstName as 'First Name',

    'y' as 'Cancel Pay',

    NULL as 'Pay #',

    NULL as 'Reg Earnings',

    2 AS 'Rate Code',

    NULL as 'Tax Frequency',

    NULL as 'Reg Hours',

    'Hours 4 Code' = CASE WHEN S IS NOT NULL

    THEN 'S'

    ELSE NULL

    END,

    'Sick Hours' = S,

    'Hours 4 Code' = CASE WHEN V IS NOT NULL

    THEN 'V'

    ELSE NULL

    END,

    'Vacation Hours' = V,

    'Hours 4 Code' = CASE WHEN O IS NOT NULL

    THEN 'O'

    ELSE NULL

    END,

    'Unknown Hours' = O

    FROM (

    SELECT

    e.EmpID

    ,e.Location

    ,e.EmpFirstName

    ,e.EmpLastName

    ,Type = CASE WHEN Type = 'Sick'

    THEN 'S'

    WHEN Type = 'Vacation'

    THEN 'V'

    ELSE 'O'

    END

    ,Total_hours = SUM(total_hours)

    FROM #tblEmployee e

    JOIN #tblTimeOffRequest r

    ON e.EmpId = r.EmpId

    GROUP BY

    e.EmpID

    ,e.Location

    ,e.EmpFirstName

    ,e.EmpLastName

    ,CASE WHEN Type = 'Sick'

    THEN 'S'

    WHEN Type = 'Vacation'

    THEN 'V'

    ELSE 'O'

    END

    ) PersonalTime

    PIVOT

    ( MAX(total_hours) FOR Type IN (, [V],[O])) as pvt

  • David Tooker (11/13/2013)


    Im still getting the same output with the new code?

    Here is the output I get with the code I posted.

    LocationBatch IDFile #Last NameFirst NameCancel PayPay #Reg EarningsRate CodeTax FrequencyReg HoursHours 4 CodeSick HoursHours 4 CodeVacation HoursHours 4 CodeUnknown Hours

    PEIEXEMPT110SmithBillyNULLNULL2NULLNULLS8.00NULLNULLO24.00

    PEIEXEMPT125JahanaJamyNULLNULL2NULLNULLS8.00V8.00NULLNULL

    PEIEXEMPT500KingDonyNULLNULL2NULLNULLS12.00V16.00NULLNULL

    You should be able to paste that into excel and it should be formatted for columns correctly.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • brad.mason5 (11/13/2013)


    I am not sure what the "new code" added means. This returns exactly same set as Sean but using PIVOT. Just a different approach. I compared the two in Actual Query Execution plan and Sean's had a lower query cost.

    SELECT

    pvt.Location,

    'EXEMPT' as 'Batch ID',

    pvt.EmpId as 'File #',

    pvt.EmpLastName as 'Last Name',

    pvt.EmpFirstName as 'First Name',

    'y' as 'Cancel Pay',

    NULL as 'Pay #',

    NULL as 'Reg Earnings',

    2 AS 'Rate Code',

    NULL as 'Tax Frequency',

    NULL as 'Reg Hours',

    'Hours 4 Code' = CASE WHEN S IS NOT NULL

    THEN 'S'

    ELSE NULL

    END,

    'Sick Hours' = S,

    'Hours 4 Code' = CASE WHEN V IS NOT NULL

    THEN 'V'

    ELSE NULL

    END,

    'Vacation Hours' = V,

    'Hours 4 Code' = CASE WHEN O IS NOT NULL

    THEN 'O'

    ELSE NULL

    END,

    'Unknown Hours' = O

    FROM (

    SELECT

    e.EmpID

    ,e.Location

    ,e.EmpFirstName

    ,e.EmpLastName

    ,Type = CASE WHEN Type = 'Sick'

    THEN 'S'

    WHEN Type = 'Vacation'

    THEN 'V'

    ELSE 'O'

    END

    ,Total_hours = SUM(total_hours)

    FROM #tblEmployee e

    JOIN #tblTimeOffRequest r

    ON e.EmpId = r.EmpId

    GROUP BY

    e.EmpID

    ,e.Location

    ,e.EmpFirstName

    ,e.EmpLastName

    ,CASE WHEN Type = 'Sick'

    THEN 'S'

    WHEN Type = 'Vacation'

    THEN 'V'

    ELSE 'O'

    END

    ) PersonalTime

    PIVOT

    ( MAX(total_hours) FOR Type IN (, [V],[O])) as pvt

    PIVOT can add some nasty performance issues. I just assumed not to use PIVOT here since the data was already aggregated. Even though your excellent code produces the same results, I tend to shy away from PIVOT as a general rule anymore having read Jeff Moden's articles about cross tabs. They tend to be more performant and are a bit easier to digest. 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I figured out what I was doing wrong. I added back to the end of statement

    GROUP BY Location,A.EmpId,EmpLastName,EmpFirstName,A.Type,A.Total_Hours.

    that caused all the rows to be displayed.

    Thanks for all the help!!

  • David Tooker (11/14/2013)


    I figured out what I was doing wrong. I added back to the end of statement

    GROUP BY Location,A.EmpId,EmpLastName,EmpFirstName,A.Type,A.Total_Hours.

    that caused all the rows to be displayed.

    Thanks for all the help!!

    You are welcome. Glad you got it sorted out and thanks for letting us know. 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply