June 5, 2014 at 1:58 am
Hello Everyone,
I'm working on a report where my table is as follows:
WITH SampleData (ID,NAME,[VALUE]) AS
(
SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'HoursPerDay','8:00'
UNION ALL SELECT 170983,'HoursPerDay','8:00'
UNION ALL SELECT 170983,'LeaveEndDate','6/16/2014'
UNION ALL SELECT 170983,'LeaveEndDate','6/16/2014'
UNION ALL SELECT 170983,'LeaveStartDate','6/16/2014'
UNION ALL SELECT 170983,'LeaveStartDate','6/16/2014'
UNION ALL SELECT 170983,'LeaveType','Personal'
UNION ALL SELECT 170983,'LeaveType','Personal'
UNION ALL SELECT 171024,'DateToday','6/04/2014'
UNION ALL SELECT 171024,'DateToday','6/04/2014'
UNION ALL SELECT 171024,'employee','1'
UNION ALL SELECT 171024,'employee','1'
UNION ALL SELECT 171024,'HoursPerDay','8:00'
UNION ALL SELECT 171024,'HoursPerDay','8:00'
UNION ALL SELECT 171024,'LeaveEndDate','6/17/2014'
UNION ALL SELECT 171024,'LeaveEndDate','6/17/2014'
UNION ALL SELECT 171024,'LeaveStartDate','6/17/2014'
UNION ALL SELECT 171024,'LeaveStartDate','6/17/2014'
UNION ALL SELECT 171024,'LeaveType','Bereavement1'
UNION ALL SELECT 171024,'LeaveType','Bereavement1'
)
SELECT
*
FROM SampleData;
Here is my query against the table above:
SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
170983NULL6/04/2014NULL NULL NULL NULL
1709831010NULL NULL NULL NULL NULL
170983NULLNULL NULL NULL 8:00 NULL
170983NULLNULL NULL 6/16/2014 NULL NULL
170983NULLNULL 6/16/2014 NULL NULL NULL
170983NULLNULL NULL NULL NULL Personal
171024NULL6/04/201 NULL NULL NULL NULL
1710241NULL NULL NULL NULL NULL
171024NULLNULL NULL NULL 8:00 NULL
171024NULLNULL NULL 6/17/2014 NULL NULL
171024NULLNULL 6/17/2014 NULL NULL NULL
171024NULLNULL NULL NULL NULL Bereavement
My Desired results are as follows:
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
17098310106/04/20146/16/2014 6/16/2014 8:00 Personal
17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement
Thank you for all the help..
June 5, 2014 at 2:04 am
When I execute your original statement, I get an error saying ID cannot be in the SELECT list because of the aggregation functions and there is no gruop by.
So I added the GROUP BY clause:
SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
GROUP BY ID
And this query gives the desired result set.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 2:11 am
Hi Koen Verbeeck,
So that's all I needed :), so I guess I had the rest setup okay.
Good deal thanks.
Take care.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply