January 30, 2017 at 1:12 pm
Hi Team,
I need help to convert columns to rows as below refer screen capture.
there is no fixed columns, it will based on data.
Thanks a lot for your help and co-operations
January 30, 2017 at 1:25 pm
soft.narayan - Monday, January 30, 2017 1:12 PMHi Team,
I need help to convert columns to rows as below refer screen capture.
there is no fixed columns, it will based on data.
Thanks a lot for your help and co-operations
I suggest turning your monitor sideways as the easiest solution to the problem.
T-SQL is great for selecting, filtering, sorting, and summarizing. It is not made for formatting data for display. Creating a dynamic cross tab display procedure can be done but it is ugly and procedurally intensive. Are there no client tools you can use to consume a resultset and display a cross tab? If you create a variable number of columns, can the client display the dynamic width results?
January 30, 2017 at 1:44 pm
soft.narayan - Monday, January 30, 2017 1:12 PMHi Team,
I need help to convert columns to rows as below refer screen capture.
there is no fixed columns, it will based on data.
Thanks a lot for your help and co-operations
If SSRS is an option, this would be a snap. Use a matrix.
January 30, 2017 at 2:22 pm
This will unpivot the data, but the column names may need some tweaking
declare @test as table (
ID int
, Title varchar(7)
, StageName varchar(20)
, [Status] varchar(7)
, TotalTime time(0)
, ActualTime time(0)
);
insert into @test( ID, Title, StageName, [Status], TotalTime, ActualTime )
values ( 1, 'Test_44', 'Receive', 'success', '00:00:30', NULL )
, ( 2, 'test_48', 'Receive', 'failed', '00:00:30', '00:00:00' )
, ( 4, 'test332', 'Receive', 'success', '00:00:30', '00:00:02' )
, ( 4, 'test332', 'Resolve', 'failed', '00:04:00', '00:00:01' )
, ( 6, 'zxzxxz', 'Receive', 'success', '00:00:30', '00:00:00' )
, ( 6, 'zxzxxz', 'Escalation Level - 2', 'failed', '00:11:00', '00:00:02' )
, ( 6, 'zxzxxz', 'Escalation Level - 3', 'success', '00:11:00', '00:00:01' )
SELECT t.ID, t.Title
, rec_TotalTime = max(case when Stagename = 'Receive' then TotalTime end)
, rec_ActualTime = max(case when Stagename = 'Receive' then ActualTime end)
, rec_Status = max(case when Stagename = 'Receive' then [Status] end)
, esc1_TotalTime = max(case when Stagename = 'Escalation Level - 1' then TotalTime end)
, esc1_ActualTime = max(case when Stagename = 'Escalation Level - 1' then ActualTime end)
, esc1_Status = max(case when Stagename = 'Escalation Level - 1' then [Status] end)
, esc2_TotalTime = max(case when Stagename = 'Escalation Level - 2' then TotalTime end)
, esc2_ActualTime = max(case when Stagename = 'Escalation Level - 2' then ActualTime end)
, esc2_Status = max(case when Stagename = 'Escalation Level - 2' then [Status] end)
, esc3_TotalTime = max(case when Stagename = 'Escalation Level - 3' then TotalTime end)
, esc3_ActualTime = max(case when Stagename = 'Escalation Level - 3' then ActualTime end)
, esc3_Status = max(case when Stagename = 'Escalation Level - 3' then [Status] end)
, res_TotalTime = max(case when Stagename = 'Resolve' then TotalTime end)
, res_ActualTime = max(case when Stagename = 'Resolve' then ActualTime end)
, res_Status = max(case when Stagename = 'Resolve' then [Status] end)
FROM @test AS t
GROUP BY t.ID, t.Title
January 30, 2017 at 2:52 pm
DesNorton - Monday, January 30, 2017 2:22 PMThis will unpivot the data, but the column names may need some tweaking
declare @test as table (
ID int
, Title varchar(7)
, StageName varchar(20)
, [Status] varchar(7)
, TotalTime time(0)
, ActualTime time(0)
);
insert into @test( ID, Title, StageName, [Status], TotalTime, ActualTime )
values ( 1, 'Test_44', 'Receive', 'success', '00:00:30', NULL )
, ( 2, 'test_48', 'Receive', 'failed', '00:00:30', '00:00:00' )
, ( 4, 'test332', 'Receive', 'success', '00:00:30', '00:00:02' )
, ( 4, 'test332', 'Resolve', 'failed', '00:04:00', '00:00:01' )
, ( 6, 'zxzxxz', 'Receive', 'success', '00:00:30', '00:00:00' )
, ( 6, 'zxzxxz', 'Escalation Level - 2', 'failed', '00:11:00', '00:00:02' )
, ( 6, 'zxzxxz', 'Escalation Level - 3', 'success', '00:11:00', '00:00:01' )
SELECT t.ID, t.Title
, rec_TotalTime = max(case when Stagename = 'Receive' then TotalTime end)
, rec_ActualTime = max(case when Stagename = 'Receive' then ActualTime end)
, rec_Status = max(case when Stagename = 'Receive' then [Status] end)
, esc1_TotalTime = max(case when Stagename = 'Escalation Level - 1' then TotalTime end)
, esc1_ActualTime = max(case when Stagename = 'Escalation Level - 1' then ActualTime end)
, esc1_Status = max(case when Stagename = 'Escalation Level - 1' then [Status] end)
, esc2_TotalTime = max(case when Stagename = 'Escalation Level - 2' then TotalTime end)
, esc2_ActualTime = max(case when Stagename = 'Escalation Level - 2' then ActualTime end)
, esc2_Status = max(case when Stagename = 'Escalation Level - 2' then [Status] end)
, esc3_TotalTime = max(case when Stagename = 'Escalation Level - 3' then TotalTime end)
, esc3_ActualTime = max(case when Stagename = 'Escalation Level - 3' then ActualTime end)
, esc3_Status = max(case when Stagename = 'Escalation Level - 3' then [Status] end)
, res_TotalTime = max(case when Stagename = 'Resolve' then TotalTime end)
, res_ActualTime = max(case when Stagename = 'Resolve' then ActualTime end)
, res_Status = max(case when Stagename = 'Resolve' then [Status] end)
FROM @test AS t
GROUP BY t.ID, t.Title
No, this will PIVOT the data. You're taking multiple rows and converting them into a single row for each group. UNPIVOT would be taking a single row and convert it into multiple rows.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 30, 2017 at 10:19 pm
pietlinden - Monday, January 30, 2017 1:44 PMsoft.narayan - Monday, January 30, 2017 1:12 PMHi Team,
I need help to convert columns to rows as below refer screen capture.
there is no fixed columns, it will based on data.
Thanks a lot for your help and co-operations
If SSRS is an option, this would be a snap. Use a matrix.
Yes ,Exactly I want to create an report with this format..Please share the link/document if you have in SSRS similar output.
January 31, 2017 at 1:09 am
Here is a little food for thought on the subject, sometimes the logic can be made more maintainable and readable by not using pivot but by segmenting it in a series of subject specific CTEs.
This is not a solution to the problem but a demonstration of an alternative technique, the logic for the Resolve is missing.USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_COL_TO_ROW') IS NOT NULL DROP TABLE dbo.TBL_COL_TO_ROW;
CREATE TABLE dbo.TBL_COL_TO_ROW
(
CTR_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_COL_TO_ROW_CTR_ID PRIMARY KEY CLUSTERED
,ID INT NOT NULL
,Title VARCHAR(10) NOT NULL
,StageName VARCHAR(25) NOT NULL
,Status VARCHAR(10) NOT NULL
,TotalTime TIME(0) NOT NULL
,ActualTime TIME(0) NULL
);
INSERT INTO dbo.TBL_COL_TO_ROW (ID,Title,StageName,Status,TotalTime,ActualTime)
VALUES (1,'Test_44','Receive' ,'success','00:30',NULL)
,(2,'Test_48','Receive' ,'failed' ,'00:30','00:00')
,(4,'test332','Receive' ,'success','00:30','00:02')
,(4,'test332','Receive' ,'failed' ,'04:00','00:01')
,(6,'zxzxxz' ,'Receive' ,'success','00:30','00:00')
,(6,'zxzxxz' ,'Escalation Level-2','failed' ,'11:00','00:02')
,(6,'zxzxxz' ,'Escalation Level-3','success','11:00','00:01')
;
;WITH RECEIVE_DATA AS
(
SELECT
TX.ID
,TX.TotalTime
,TX.ActualTime
,TX.Status
FROM dbo.TBL_COL_TO_ROW TX
WHERE TX.StageName = 'Receive'
)
,ESCALATION_0NE AS
(
SELECT
TX.ID
,TX.TotalTime
,TX.ActualTime
,TX.Status
FROM dbo.TBL_COL_TO_ROW TX
WHERE TX.StageName = 'Escalation Level-1'
)
,ESCALATION_TWO AS
(
SELECT
TX.ID
,TX.TotalTime
,TX.ActualTime
,TX.Status
FROM dbo.TBL_COL_TO_ROW TX
WHERE TX.StageName = 'Escalation Level-2'
)
,ESCALATION_THREE AS
(
SELECT
TX.ID
,TX.TotalTime
,TX.ActualTime
,TX.Status
FROM dbo.TBL_COL_TO_ROW TX
WHERE TX.StageName = 'Escalation Level-3'
)
,UNIQUE_ENTRIES AS
(
SELECT
TX.ID
,TX.Title
FROM dbo.TBL_COL_TO_ROW TX
GROUP BY TX.ID
,TX.Title
)
SELECT
UE.ID
,UE.Title
,RD.TotalTime
,RD.ActualTime
,RD.Status
,E1.TotalTime
,E1.ActualTime
,E1.Status
,E2.TotalTime
,E2.ActualTime
,E2.Status
,E3.TotalTime
,E3.ActualTime
,E3.Status
FROM UNIQUE_ENTRIES UE
LEFT OUTER JOIN RECEIVE_DATA RD
ON UE.ID = RD.ID
LEFT OUTER JOIN ESCALATION_0NE E1
ON UE.ID = E1.ID
LEFT OUTER JOIN ESCALATION_TWO E2
ON UE.ID = E2.ID
LEFT OUTER JOIN ESCALATION_THREE E3
ON UE.ID = E3.ID;
Output
ID Title TotalTime ActualTime Status TotalTime ActualTime Status TotalTime ActualTime Status TotalTime ActualTime Status
----------- ---------- ---------------- ---------------- ---------- ---------------- ---------------- ---------- ---------------- ---------------- ---------- ---------------- ---------------- ----------
1 Test_44 00:30:00 NULL success NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 Test_48 00:30:00 00:00:00 failed NULL NULL NULL NULL NULL NULL NULL NULL NULL
4 test332 00:30:00 00:02:00 success NULL NULL NULL NULL NULL NULL NULL NULL NULL
4 test332 04:00:00 00:01:00 failed NULL NULL NULL NULL NULL NULL NULL NULL NULL
6 zxzxxz 00:30:00 00:00:00 success NULL NULL NULL 11:00:00 00:02:00 failed 11:00:00 00:01:00 success
February 3, 2017 at 10:10 am
Thanks a lot DesNorton & Eirikur Eiriksson for your help and co-operation , grate solution .
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy