convert column to rows
-
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
- 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?
- 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.
-
This will unpivot the data, but the column names may need some tweaking
declare @test-2 as table (
ID int
, Title varchar(7)
, StageName varchar(20)
, [Status] varchar(7)
, TotalTime time(0)
, ActualTime time(0)
);insert into @test-2( 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-2 AS t
GROUP BY t.ID, t.Title - DesNorton - Monday, January 30, 2017 2:22 PM
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 - 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.
-
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 -
Thanks a lot DesNorton & Eirikur Eiriksson for your help and co-operation , grate solution .
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply