Pivot problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot problem

Author

Topic

b8538230
Starting Member

16 Posts

Posted - 2012-02-23 : 14:02:18

I'm trying to write a pivot for my ticketing system. Here's the query:


	select incident_number,  taskid_mtsa, dispatch, acknowledged, onsite
	from (
		-- Subquery to return dispatch timestamps as pivoted rows
		select incident_number, work_log_date, taskid_mtsa,
			-- translate worklog descriptions to easy to use names
			datetype = case
				when DETAILED_DESCRIPTION like 'Dispatch Created:%'
					then 'Dispatch'
				when DETAILED_DESCRIPTION like 'Dispatch Assigned: ETA%'
					then 'Acknowledged'
				when DETAILED_DESCRIPTION like 'Dispatch On Site:%'
					then 'Onsite'
			end,
			
			-- Use partitioning to zero in on the task id and order by timestamp.
			rn = row_number() over (
				partition by incident_number, taskid_mtsa order by submit_date
				)
			
		from or_rcip.dbo.worklog
		where  DETAILED_DESCRIPTION like 'Dispatch%:%'
		and INCIDENT_NUMBER = 'INC000002100564'
		) as src
		
		-- Use pivoting to get the individual rows into columns.
		pivot (
			min(work_log_date)
			for datetype in ([Dispatch], [Acknowledged], [Onsite]) 
		) as pvt

The inner select produces this table:


incident_number	work_log_date	        taskid_mtsa	datetype	rn
INC000002100564	2012-02-17 20:04:16.000	TAS000000033205	Dispatch	1
INC000002100564	2012-02-17 21:28:56.000	TAS000000033205	Onsite	        2
INC000002100564	2012-02-17 21:30:55.000	TAS000000033205	NULL	        3
INC000002100564	2012-02-21 20:07:47.000	TAS000000033537	Dispatch	1
INC000002100564	2012-02-21 20:24:56.000	TAS000000033537	Acknowledged	2
INC000002100564	2012-02-21 20:25:08.000	TAS000000033537	Onsite	        3
INC000002100564	2012-02-21 20:26:09.000	TAS000000033537	NULL	        4

After pivoting I get:


incident_number	taskid_mtsa	dispatch	        acknowledged	                onsite
INC000002100564	TAS000000033205	2012-02-17 20:04:16.000	NULL	                        NULL
INC000002100564	TAS000000033205	NULL	                NULL	                        2012-02-17 21:28:56.000
INC000002100564	TAS000000033205	NULL	                NULL	                        NULL
INC000002100564	TAS000000033537	2012-02-21 20:07:47.000	NULL	                        NULL
INC000002100564	TAS000000033537	NULL	                2012-02-21 20:24:56.000	        NULL
INC000002100564	TAS000000033537	NULL	                NULL	                        2012-02-21 20:25:08.000
INC000002100564	TAS000000033537	NULL	                NULL	                        NULL

What I want is:


incident_number	taskid_mtsa	dispatch	        acknowledged	                onsite
INC000002100564	TAS000000033205	2012-02-17 20:04:16.000	NULL	                        2012-02-17 21:28:56.000
INC000002100564	TAS000000033537	2012-02-21 20:07:47.000	2012-02-21 20:24:56.000	        2012-02-21 20:25:08.000

How should I change my query to achieve this result?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 14:39:56

why should you need rn? i think thats wahts is causing issue

try this instead and see


select incident_number,  taskid_mtsa, dispatch, acknowledged, onsite
	from (
		-- Subquery to return dispatch timestamps as pivoted rows
		select incident_number, work_log_date, taskid_mtsa,
			-- translate worklog descriptions to easy to use names
			datetype = case
				when DETAILED_DESCRIPTION like 'Dispatch Created:%'
					then 'Dispatch'
				when DETAILED_DESCRIPTION like 'Dispatch Assigned: ETA%'
					then 'Acknowledged'
				when DETAILED_DESCRIPTION like 'Dispatch On Site:%'
					then 'Onsite'
			end			
		from or_rcip.dbo.worklog
		where  DETAILED_DESCRIPTION like 'Dispatch%:%'
		and INCIDENT_NUMBER = 'INC000002100564'
		) as src
		
		-- Use pivoting to get the individual rows into columns.
		pivot (
			min(work_log_date)
			for datetype in ([Dispatch], [Acknowledged], [Onsite]) 
		) as pvt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

b8538230
Starting Member

16 Posts

Posted - 2012-02-23 : 15:18:23

Yeah that did it.
I was using rn only to achieve the partitioning. No other reason

b8538230
Starting Member

16 Posts

Posted - 2012-02-23 : 15:29:02

Yeah! that did it (wish I knew why!)

how would you modify your suggestion so that only records with the minimum taskid_mtsa per incident were returned?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 15:38:27

[code]
select incident_number, taskid_mtsa, dispatch, acknowledged, onsite
from
(
select incident_number, taskid_mtsa, dispatch, acknowledged, onsite,
row_number() over (partition by incident_number order by taskid_mtsa) as rn
from (
-- Subquery to return dispatch timestamps as pivoted rows
select incident_number, work_log_date, taskid_mtsa,
-- translate worklog descriptions to easy to use names
datetype = case
when DETAILED_DESCRIPTION like 'Dispatch Created:%'
then 'Dispatch'
when DETAILED_DESCRIPTION like 'Dispatch Assigned: ETA%'
then 'Acknowledged'
when DETAILED_DESCRIPTION like 'Dispatch On Site:%'
then 'Onsite'
end
from or_rcip.dbo.worklog
where DETAILED_DESCRIPTION like 'Dispatch%:%'
and INCIDENT_NUMBER = 'INC000002100564'
) as src

-- Use pivoting to get the individual rows into columns.
pivot (
min(work_log_date)
for datetype in ([Dispatch], [Acknowledged], [Onsite])
) as pvt
)r
where rn=1

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources