Pivot data order by result time

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 data order by result time

Author

Topic

yoshidahiro
Starting Member

2 Posts

Posted - 2015-04-17 : 13:07:11

I am querying a table with multiple rows

SELECT caseId, Name ,CompletedDate
FROM TableZ

the number of results per CaseID are varable some have 1 record others up to 36 records
I need to pivot the results based off of the competed date

Name has 20 distinct values but we can have diplicates
so something like

Case Name Date
1 This 2015-01-01
1 That 2015-01-03
2 That 2013-01-01
2 Then 2014-12-03
2 This 2015-01-01
2 There 2015-04-03
3 This 1999-01-01
3 That 2001-01-02
3 This 2006-01-01

looks like this

1 This That
2 That Then This There
3 This That This

yoshidahiro
Starting Member

2 Posts

Posted - 2015-04-17 : 13:08:24

BTW I dont really care what the column names are but I would like to be able to do
Step1 step2 as column names if possible

kostya1122
Starting Member

15 Posts

Posted - 2015-04-20 : 21:09:01

something like this
select * , row_number() over ( partition by case order by date desc) as row
into #table
from table

select case
,max([1]) as step1
,max([2]) as step2
,max([3]) as step3
from table
pivot
(
max(name)
for row in ([1],[2],[3])
) as pvt
group by case

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources