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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Can PIVOT be used to do this?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kaos_king
Starting Member

13 Posts

Posted - 04/10/2015 :  04:41:08  Show Profile  Reply with Quote
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

Romania
545 Posts

Posted - 04/10/2015 :  06:09:27  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 04/10/2015 :  06:14:09  Show Profile  Reply with Quote
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
Go to Top of Page

kaos_king
Starting Member

13 Posts

Posted - 04/10/2015 :  09:57:57  Show Profile  Reply with Quote
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 :)
Go to Top of Page

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 04/10/2015 :  11:12:09  Show Profile  Reply with Quote
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
Go to Top of Page

kaos_king
Starting Member

13 Posts

Posted - 04/13/2015 :  05:11:36  Show Profile  Reply with Quote
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).
Go to Top of Page

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 04/13/2015 :  05:46:10  Show Profile  Reply with Quote

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



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.

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



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




sabinWeb MCP
Go to Top of Page

kaos_king
Starting Member

13 Posts

Posted - 04/13/2015 :  11:30:01  Show Profile  Reply with Quote
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!
Go to Top of Page

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 04/13/2015 :  13:14:12  Show Profile  Reply with Quote
Welcome!


sabinWeb MCP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000