I am trying to figure out a way to transpose my data into rows grouped by specific clusters. I already ran a query which displays the data vertically but I wonder how I can transpose this.
Here is how my data after my query looks like (which I put into a temp table):
App Old_Status_ID New_Status_ID Status_Change_Date UserID
A 1 2 2015_01_01 22
A 2 3 2015_02_01 20
A 3 4 2015_03_20 51
B 1 2 2015_01_25 84
B 2 3 2015_02_11 22
C 1 2 2015_01_02 35
C 2 3 2015_03_10 01
C 3 4 2015_04_05 55
....
The abovementioned table has hundreds of different Apps, 7 different Statuses and hundreds of users. What I am trying to do is to display all changes within the App just in one row. In addition, I want to include the difference of the elapsed time between the status changes in days (ΔStatus_Change_Date) = ΔSCD.
Here is an example of how the datasheet could like:
App Status1A Status1B User1 ΔSCP_1 Status_2A Status_2B User2 ΔSCP_2 ...
A 1 2 22 0 2 3 20 31 ...
B 1 2 84 0 2 3 22 17 ...
Unfortunately not everything fits into the row here but I hope that you will understand the concept and my goal with the example.
How can I transpose or write a query to achieve that the associated data from one App is in one row?
I really appreciate your help!!!
Here is some sample data:
+-------+-------------+-------------+------------------+--------+
| app | OldStatusId | NewStatusId | StatusChangeDate | userid |
+-------+-------------+-------------+------------------+--------+
| 16195 | 1 | 32 | 2017-10-03 | 2137 |
| 16195 | 32 | 32 | 2017-10-03 | 2137 |
| 16195 | 32 | 8 | 2018-01-10 | 6539 |
| 16195 | 8 | 2 | 2018-01-12 | 3452 |
| 16505 | 1 | 1 | 2017-04-26 | 3551 |
| 16505 | 1 | 32 | 2017-05-24 | 2063 |
| 16505 | 32 | 32 | 2017-05-24 | 2063 |
| 16505 | 1 | 1 | 2017-06-23 | 3551 |
| 16505 | 32 | 4 | 2017-06-23 | 5291 |
| 16505 | 4 | 32 | 2017-06-26 | 2063 |
| 16505 | 32 | 8 | 2017-06-26 | 5291 |
| 16505 | 2 | 2 | 2017-06-28 | 3438 |
| 16505 | 8 | 2 | 2017-06-28 | 3438 |
| 16505 | 1 | 32 | 2017-08-28 | 2063 |
| 16505 | 32 | 4 | 2017-10-03 | 5291 |
| 16505 | 4 | 32 | 2017-10-04 | 2063 |
| 16505 | 2 | 2 | 2017-10-25 | 3438 |
| 16505 | 8 | 2 | 2017-10-25 | 3438 |
| 16505 | 32 | 8 | 2017-10-25 | 5291 |
| 16515 | 1 | 32 | 2017-06-01 | 2456 |
| 16515 | 32 | 32 | 2017-06-01 | 2456 |
| 16515 | 4 | 4 | 2017-07-25 | 5291 |
| 16515 | 32 | 4 | 2017-07-25 | 5291 |
| 16515 | 4 | 32 | 2017-07-27 | 2456 |
| 16515 | 32 | 4 | 2017-08-09 | 5291 |
| 16515 | 4 | 32 | 2017-08-10 | 2456 |
| 16515 | 32 | 8 | 2017-08-24 | 5291 |
| 16515 | 2 | 2 | 2017-08-28 | 3438 |
| 16515 | 8 | 2 | 2017-08-28 | 3438 |
| 16515 | 1 | 32 | 2017-10-06 | 2456 |
| 16515 | 32 | 32 | 2017-10-06 | 2456 |
| 16515 | 1 | 1 | 2017-10-17 | 2456 |
| 16515 | 32 | 128 | 2017-11-20 | 5291 |
| 16515 | 32 | 8 | 2017-11-29 | 5291 |
| 16515 | 128 | 32 | 2017-11-29 | 5291 |
| 16515 | 8 | 2 | 2017-12-07 | 3611 |
+-------+-------------+-------------+------------------+--------+
I will leave the ordering issue to you. As I stated previously when you have two rows with the same date you cannot know which row will be listed first because you don't have any way of doing that with your data. What you need here is some pretty ugly dynamic sql to generate all these columns. In this code I am going to use a tally table. In my system I keep this as a view. Here is the code for my tally table.
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
GO
Now we need to leverage dynamic sql and this tally table. We are going to have sql build our sql for us. Something like this.
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
app int
, OldStatusId int
, NewStatusId int
, StatusChangeDate date
, userid int
)
insert #Something
(
app
, OldStatusId
, NewStatusId
, StatusChangeDate
, userid
) VALUES
(16195, 1, 32, '2017-10-03', 2137)
, (16195, 32, 32, '2017-10-03', 2137)
, (16195, 32, 8, '2018-01-10', 6539)
, (16195, 8, 2, '2018-01-12', 3452)
, (16505, 1, 1, '2017-04-26', 3551)
, (16505, 1, 32, '2017-05-24', 2063)
, (16505, 32, 32, '2017-05-24', 2063)
, (16505, 1, 1, '2017-06-23', 3551)
, (16505, 32, 4, '2017-06-23', 5291)
, (16505, 4, 32, '2017-06-26', 2063)
, (16505, 32, 8, '2017-06-26', 5291)
, (16505, 2, 2, '2017-06-28', 3438)
, (16505, 8, 2, '2017-06-28', 3438)
, (16505, 1, 32, '2017-08-28', 2063)
, (16505, 32, 4, '2017-10-03', 5291)
, (16505, 4, 32, '2017-10-04', 2063)
, (16505, 2, 2, '2017-10-25', 3438)
, (16505, 8, 2, '2017-10-25', 3438)
, (16505, 32, 8, '2017-10-25', 5291)
, (16515, 1, 32, '2017-06-01', 2456)
, (16515, 32, 32, '2017-06-01', 2456)
, (16515, 4, 4, '2017-07-25', 5291)
, (16515, 32, 4, '2017-07-25', 5291)
, (16515, 4, 32, '2017-07-27', 2456)
, (16515, 32, 4, '2017-08-09', 5291)
, (16515, 4, 32, '2017-08-10', 2456)
, (16515, 32, 8, '2017-08-24', 5291)
, (16515, 2, 2, '2017-08-28', 3438)
, (16515, 8, 2, '2017-08-28', 3438)
, (16515, 1, 32, '2017-10-06', 2456)
, (16515, 32, 32, '2017-10-06', 2456)
, (16515, 1, 1, '2017-10-17', 2456)
, (16515, 32, 28, '2017-11-20', 5291)
, (16515, 32, 8, '2017-11-29', 5291)
, (16515, 128, 32, '2017-11-29', 5291)
, (16515, 8, 2, '2017-12-07', 3611)
declare @StaticPortion nvarchar(2000) =
'with OrderedResults as
(
select *, ROW_NUMBER() over(partition by app order by StatusChangeDate) as RowNum
from #Something
)
select app';
declare @DynamicPortion nvarchar(max) = '';
select @DynamicPortion = @DynamicPortion +
', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then OldStatusId end) as OldStatus' + CAST(N as varchar(6)) + CHAR(10)
+ ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then NewStatusId end) as NewStatus' + CAST(N as varchar(6)) + CHAR(10)
+ ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then StatusChangeDate end) as StatusChangeDate' + CAST(N as varchar(6)) + CHAR(10)
+ ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then userid end) as userid' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <=
(
select top 1 Count(*)
from #Something
group by app
order by COUNT(*) desc
)
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults Group by app order by app';
declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;
exec sp_executesql @SqlToExecute
The only part of this I did not demonstrate here is getting the change from row to row. You could do this with a temp table. But you would have to use a global temp table because the columns are being generated dynamically and the scope of the temp table would not let us view it after we execute the dynamic query. Once you understand what this code is doing you should be able to add the last part yourself. But if you get stuck post back and we will see what we can do.