Pivot Query

Question

Post reply

Pivot Query

delizat4g

Old Hand

Points: 369

December 10, 2019 at 8:38 pm

Go to Answer

#3706674

/*
I'm trying to write a query that uses a list of dates as a header
and lists, lets say, employees and if they were in training on that day.
I've made a start, but I can't get a pivot working
Note: This, by necessity, is a made up scenario similar to what I'm trying to do.

/@@@@@@@@@@@/
Hopeful Result:
/@@@@@@@@@@@/
12/1/201912/2/201912/3/201912/4/2019 12/5/2019
AliceYES                        YES
BettYES        YES
Maya                YES

/@@@@@@@@@@@/
example table
During the Winter semester (12/01/2019 - 12/31/2019) who was in training
/@@@@@@@@@@@/
EmployeeTrainingDtgTrainingType
Alice        12/1/2019Red
Alice        12/1/2019Green
Alice        12/4/2019White
Bett        12/1/2019Red
Bett        12/2/2019Green
Maya        12/3/2019Red
Maya        12/3/2019Green

/@@@@@@@@@@@/
I've started with a temp table to hold the dates
/@@@@@@@@@@@/
ex:
*/
;with ctedaterange(begDtg, endDtg)
as
(
select begDtg, dateadd(d, -1, endDtg) as endDtg /*sub a day so qry doesn't return an extra day*/
from trainingSemesters t
where semesterName = 'Winter'
union all
select dateadd(dd, 1, begDtg), endDtg
from cetdaterange where begDTG <= endDtg
)

select c.begDtg
into #tempDateList 
from cteDateRange c
/*
/@@@@@@@@@@@/
scripts
/@@@@@@@@@@@/
*/
create table [dbo].[trainingEmployee] (
[_id] [int] IDENTITY(1,1) NOT NULL,
[employee] [nchar](10) NULL,
[trainingDtg] [date] NULL,
[trainingType] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Alice', '2019-12-01', 'Red')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Alice', '2019-12-01', 'Green')
insert into trainingEmployee (employee, trainingDtg, trainingType)
values ('Alice', '2019-12-04', 'White')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Bett', '2019-12-01', 'Red')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Bett', '2019-12-02', 'Green')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Maya', '2019-12-03', 'Red')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Maya', '2019-12-03', 'Green')

create table [dbo].[trainingSemester] (
[_id] [int] IDENTITY(1,1) NOT NULL,
[begDtg] [date] NULL,
[endDtg] [date] NULL,
[semesterName] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into trainingSemester(begDtg, endDtg, semesterName)
values ('2019-12-01', '2019-12-31', 'Winter')
insert into trainingSemester(begDtg, endDtg, semesterName)
values ('2019-06-01', '2019-06-30', 'Summer')

This topic was modified 4 years, 4 months ago by delizat4g.
This topic was modified 4 years, 4 months ago by delizat4g.
This topic was modified 4 years, 4 months ago by delizat4g.
This topic was modified 4 years, 4 months ago by delizat4g.
This topic was modified 4 years, 4 months ago by delizat4g.
This topic was modified 4 years, 4 months ago by delizat4g.
This topic was modified 4 years, 4 months ago by delizat4g.

Code-Blooded

ScottPletcher

SSC Guru

Points: 100946

December 10, 2019 at 10:11 pm

Answer

#3706715

Let me know if this code looks good, and we can convert it into more dynamic code. I didn't want to spend time doing that until I had the core code working the way you wanted it to first.

DECLARE @end_date date
DECLARE @semester_name nvarchar(10)
DECLARE @sql nvarchar(max)
DECLARE @start_date date

SET @semester_name = 'Winter'

SELECT TOP (1) @start_date = begDtg, @end_date = endDtg
FROM dbo.trainingSemester
WHERE semesterName = @semester_name
ORDER BY begDtg DESC

SELECT employee,
    ISNULL([2019-12-01], '') AS [2019-12-01],
    ISNULL([2019-12-02], '') AS [2019-12-02],
    ISNULL([2019-12-03], '') AS [2019-12-03],
    ISNULL([2019-12-04], '') AS [2019-12-04],
    ISNULL([2019-12-05], '') AS [2019-12-05]
FROM (
    SELECT employee, trainingDtg, trainingType
    FROM dbo.trainingEmployee
    WHERE trainingDtg BETWEEN @start_date AND @end_date
) AS tE
PIVOT ( MAX(tE.trainingType) FOR trainingDtg IN ([2019-12-01], [2019-12-02],
        [2019-12-03], [2019-12-04], [2019-12-05]) ) AS pvt

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 8 posts - 1 through 7 (of 7 total)

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

ScottPletcher SSC Guru Points: 100946 More actions · Answer 1

Could you please post directly usable data, i.e. CREATE TABLE and INSERT statements for the example table data.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

delizat4g Old Hand Points: 369 More actions · Answer 2

Yes...I've added them to the bottom of the OP.

Thanks, D

Code-Blooded

delizat4g Old Hand Points: 369 More actions · Answer 3

Thanks.

This gives me a clean header.

employee 12/1/2019 12/2/2019 12/3/2019 12/4/2019 12/5/2019

This reply was modified 4 years, 4 months ago by delizat4g.
This reply was modified 4 years, 4 months ago by delizat4g.

Code-Blooded

delizat4g Old Hand Points: 369 More actions · Answer 4

This works great for me.

How would I go about grabbing the dates dynamically?

Thanks!, D

Code-Blooded

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

delizat4g wrote:

This works great for me.
How would I go about grabbing the dates dynamically?
Thanks!, D

With the understanding that I have no love for the Pivot operator, please see the following...

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100946 More actions · Answer 6

Here it is.

DECLARE @end_date date
DECLARE @semester_name nvarchar(10)
DECLARE @sql nvarchar(max)
DECLARE @sql2 nvarchar(max)
DECLARE @start_date date

SET @semester_name = 'Winter'

SELECT TOP (1) @start_date = begDtg, @end_date = endDtg
FROM dbo.trainingSemester
WHERE semesterName = @semester_name
ORDER BY begDtg DESC

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT @sql = STUFF((
    SELECT ',ISNULL([' + CONVERT(varchar(10), DATEADD(DAY, t.number, @start_date), 120) +
        '], '''') AS [' + CONVERT(varchar(10), DATEADD(DAY, t.number, @start_date), 120) + ']'
    FROM cte_tally1000 t
    WHERE t.number BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date)
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
    , 1, 1, '')
--PRINT @sql

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT @sql2 = STUFF((
    SELECT ',[' + CONVERT(varchar(10), DATEADD(DAY, t.number, @start_date), 120) + ']'
    FROM cte_tally1000 t
    WHERE t.number BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date)
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
    , 1, 1, '')
--PRINT @sql2

SET @sql = '
SELECT employee, ' + @sql + '
FROM (
    SELECT employee, trainingDtg, trainingType
    FROM dbo.trainingEmployee
    WHERE trainingDtg BETWEEN @start_date AND @end_date
) AS tE
PIVOT ( MAX(tE.trainingType) FOR trainingDtg IN (' + @sql2 + ') ) AS pvt'

PRINT @sql
EXEC sp_executesql @sql, N'@start_date date, @end_date date', @start_date, @end_date

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.