Can PIVOT be used to do this? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Can PIVOT be used to do this?

Author

Topic

kaos_king
Starting Member

13 Posts

Posted - 2015-04-10 : 04:41:08

Hi guys, I have a set of data that is populated from two tables. the first table contains the unique identifier (a case number) and the second has events about that particular case.

Imagine my query pulls 2 cases back, one has 3 events and the other has 4, therefore there is 7 rows of data and they look something like this:

CaseRef Event Rownum
123 Open 1
123 Update 2
123 Close 3
456 Open 1
456 Alter 2
456 Update 3
456 Close 4

(Rownum has been populated using row_number over partition on the event entrytime).

I am attempting to have the data display in a linear format like this:

CaseRef Event 1 Event 2 Event 3 Event 4 Event 5
123 Open Update Close
456 Open Alter Update Close

So you can see left to right what events happened in order. I was looking at doing this with a PIVOT table however I don't think I can do it with that. Also, there is no way of knowing how many events there will be although it should never be over 10 so I could hard code the 10 columns but even better would be a dynamic method.

Am I missing an obvious way? My brain is hurting

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-10 : 06:09:27

You can search on web for dynamic version of PIVOT
This version is with fix number of columns = 5


;WITH cteSample
AS(
	SELECT CaseRef,Event,Rownum
	FROM (VALUES(123,'Open',1)
			,(123,'Update',2)
			,(123,'Close',3)
			,(456,'Open',1)
			,(456,'Alter',2)
			,(456,'Update',3)
			,(456,'Close',4)) AS A   (CaseRef,Event,Rownum)
)

SELECT 
	B.CaseRef
	,B.[1] as Event1
	,B.[2] as Event2
	,B.[3] as Event3
	,B.[4] as Event4
	,B.[5] as Event5
FROM cteSample AS A
PIVOT(
	MIN(Event)
	FOR RowNum IN ([1],[2],[3],[4],[5]) ) As B

and the result set:


CaseRef	Event1	Event2	Event3	Event4	Event5
123	Open	Update	Close	NULL	NULL
456	Open	Alter	Update	Close	NULL

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-10 : 06:14:09

I used VALUES, and this is not available in 2005 .
So :


DECLARE @tSample TABLE
(
	CaseRef INT
	,Event VARCHAR(10)
	,Rownum INT
)
INSERT INTO @tSample (CaseRef,Event,Rownum)
SELECT 123,'Open',1 UNION ALL
SELECT 123,'Update',2 UNION ALL
SELECT 123,'Close',3  UNION ALL
SELECT 456,'Open',1 UNION ALL
SELECT 456,'Alter',2 UNION ALL
SELECT 456,'Update',3 UNION ALL
SELECT 456,'Close',4
	

SELECT 
	B.CaseRef
	,B.[1] as Event1
	,B.[2] as Event2
	,B.[3] as Event3
	,B.[4] as Event4
	,B.[5] as Event5
FROM @tSample AS A
PIVOT(
	MIN(Event)
	FOR RowNum IN ([1],[2],[3],[4],[5]) 
) As B

sabinWeb MCP

kaos_king
Starting Member

13 Posts

Posted - 2015-04-10 : 09:57:57

Amazingly fast response, thank you.

Your sample works perfectly, however when I apply this to my set of data, I am getting a separate row for each event although each event is in the correct column, the result looks a little like this:

(I've used an image as the last set didn't display the same when I posted)

As far as I can tell I have done nothing different to yourself, I can only assume its the data I'm working with? I can supply an example if you wish?

Once again, thank you for helping :)

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-10 : 11:12:09

Can you post your final query ?
I am guessing that are other columns involve , (beside CaseRef,Event,Rownum),that are "spliting" the output.

sabinWeb MCP

kaos_king
Starting Member

13 Posts

Posted - 2015-04-13 : 05:11:36

The final query is very similar:

SELECT 
	B.CaseRef
	,B.[1] as CaseType1
	,B.[2] as CaseType2
	,B.[3] as CaseType3
	,B.[4] as CaseType4
	,B.[5] as CaseType5
	,B.[6] as CaseType6
	,B.[7] as CaseType7
	,B. as CaseType8
	,B.[9] as CaseType9
	,B.[10] as CaseType10
FROM @finaldata AS A
PIVOT(
	MIN(eventdescription)
	FOR rownum IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10]) 
) As B

There are a couple of temporary tables that gather the information, however the final table contains only a small set of data for my testing. This is a screenshot of the whole table contents - Clicky (I've blurred the events out as they are semi-sensitive).

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-13 : 05:46:10

[code]
DECLARE @tSample TABLE
(
CaseRef VARCHAR(50)
,Event VARCHAR(10)
,Rownum INT
,ActiveDate DATETIME
,EntryDate DATETIME
)
INSERT INTO @tSample (CaseRef,Event,Rownum,ActiveDate,EntryDate)
SELECT 'f7ceb6ad','CASECHANGE',1,'20100806', '20100806 01:58:56' UNION ALL
SELECT 'f7ceb6ad','CASECHANGE',2,'20100806', '20100806 01:59:01' UNION ALL
SELECT 'f7ceb6ad','CASECHANGE',3,'20100806', '20100806 02:28:01' UNION ALL
SELECT 'c4d20582','CASECHANGE',1,'20110330', '20110330 10:34:16'UNION ALL
SELECT 'c4d20582','CASECHANGE',2,'20110330', '20110330 11:01:16'

SELECT
B.CaseRef
,B.[1] as CaseType1
,B.[2] as CaseType2
,B.[3] as CaseType3
,B.[4] as CaseType4
,B.[5] as CaseType5
,B.[6] as CaseType6
,B.[7] as CaseType7
,B.

as CaseType8
,B.[9] as CaseType9
,B.[10] as CaseType10
FROM @tSample AS A
PIVOT(
MIN(Event)
FOR rownum IN ([1],[2],[3],[4],[5],[6],[7],

,[9],[10])
) As B

;WITH cteSource
AS(SELECT CaseRef , Event, Rownum FROM @tSample)

SELECT
B.CaseRef
,B.[1] as CaseType1
,B.[2] as CaseType2
,B.[3] as CaseType3
,B.[4] as CaseType4
,B.[5] as CaseType5
,B.[6] as CaseType6
,B.[7] as CaseType7
,B.

as CaseType8
,B.[9] as CaseType9
,B.[10] as CaseType10
--FROM @tSample AS A
FROM cteSource AS A
PIVOT(
MIN(Event)
FOR rownum IN ([1],[2],[3],[4],[5],[6],[7],

,[9],[10])
) As B
[/code]

First query is using the entire source (all the columns).
The second query is using only the column needed. The extra columns are generating the splitting of the rows.
[code]
CaseRef CaseType1 CaseType2 CaseType3 CaseType4 CaseType5 CaseType6 CaseType7 CaseType8 CaseType9 CaseType10
c4d20582 CASECHANGE NULL NULL NULL NULL NULL NULL NULL NULL NULL
c4d20582 NULL CASECHANGE NULL NULL NULL NULL NULL NULL NULL NULL
f7ceb6ad CASECHANGE NULL NULL NULL NULL NULL NULL NULL NULL NULL
f7ceb6ad NULL CASECHANGE NULL NULL NULL NULL NULL NULL NULL NULL
f7ceb6ad NULL NULL CASECHANGE NULL NULL NULL NULL NULL NULL NULL
[/code]

[code]
CaseRef CaseType1 CaseType2 CaseType3 CaseType4 CaseType5 CaseType6 CaseType7 CaseType8 CaseType9 CaseType10
c4d20582 CASECHANGE CASECHANGE NULL NULL NULL NULL NULL NULL NULL NULL
f7ceb6ad CASECHANGE CASECHANGE CASECHANGE NULL NULL NULL NULL NULL NULL NULL
[/code]

sabinWeb MCP

kaos_king
Starting Member

13 Posts

Posted - 2015-04-13 : 11:30:01

Brilliant. Thank you kindly for your help.

It seems the entrydate of the event is separating it. I have just ommited that as we don't really need the info. I've added in a couple of other columns to the final output and it still displays perfectly.

Thank you!

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-13 : 13:14:12

Welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources