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 12»»

How to combine 3 rows into one Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:58 AM
Points: 196, Visits: 144
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
Post #1513953
Posted Wednesday, November 13, 2013 9:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1513965
Posted Wednesday, November 13, 2013 10:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:58 AM
Points: 196, Visits: 144
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
Post #1513988
Posted Wednesday, November 13, 2013 11:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1514009
Posted Wednesday, November 13, 2013 1:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:58 AM
Points: 196, Visits: 144
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 hrs4Amt hrs4Code hrs4Amt hrs4codehrs4Amt
K00 EXEMPT 500 King Don y NULL NULL 2 NULL NULL S 4.00 NULL NULL NULL NULL
K00 EXEMPT 500 King Don y NULL NULL 2 NULL NULL S 8.00 NULL NULL NULL NULL
K00 EXEMPT 500 King Don y NULL NULL 2 NULL NULL NULL NULL V 16.00 NULL NULL

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
Post #1514054
Posted Wednesday, November 13, 2013 2:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1514070
Posted Wednesday, November 13, 2013 2:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:58 AM
Points: 196, Visits: 144
Im still getting the same output with the new code?
Post #1514074
Posted Wednesday, November 13, 2013 3:18 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:40 PM
Points: 1,046, Visits: 1,208
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 ([S], [V],[O])) as pvt

Post #1514077
Posted Wednesday, November 13, 2013 3:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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.

Location	Batch ID	File #	Last Name	First Name	Cancel Pay	Pay #	Reg Earnings	Rate Code	Tax Frequency	Reg Hours	Hours 4 Code	Sick Hours	Hours 4 Code	Vacation Hours	Hours 4 Code	Unknown Hours
PEI EXEMPT 110 Smith Bill y NULL NULL 2 NULL NULL S 8.00 NULL NULL O 24.00
PEI EXEMPT 125 Jahana Jam y NULL NULL 2 NULL NULL S 8.00 V 8.00 NULL NULL
PEI EXEMPT 500 King Don y NULL NULL 2 NULL NULL S 12.00 V 16.00 NULL NULL

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1514080
Posted Wednesday, November 13, 2013 3:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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 ([S], [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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1514083
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse