T-SQL Matrix– Sample Code


CREATE TABLE [dbo].[test](
	[PROD_GRP] [nvarchar](255) NULL,
	[CaseType] [nvarchar](255) NULL,
	[NumCO] [float] NULL,
	[NumCC] [float] NULL,
	[NumLR] [float] NULL,
	[NumLC] [float] NULL,
	[AvgResp] [float] NULL,
	[AvgRest] [float] NULL,
	[BillTime] [float] NULL,
	[NonBillTime] [float] NULL,
	[TotalTime] [float] NULL

INSERT [dbo].[test] 
([PROD_GRP], [CaseType], [NumCO], [NumCC], [NumLR], [NumLC], [AvgResp], [AvgRest], [BillTime], [NonBillTime], [TotalTime])
(N'GLADIATOR', N'CWR', 22, 17, 0, 0, 760, 5224, 0.34, 0.1, 0.43),
(N'GLADIATOR', N'SUP', 811, 809, 44, 21, 21, 1430, 0.63, 6.01, 6.65),
(N'MATRIX', N'CWR', 2, 1, 1, 0, -15689, 123, 0.02, 0, 0.02),
(N'MATRIX', N'SUP', 345, 360, 45, 10, 26, 1817, 11.83, 0.75, 12.58)

;with mycte
as (
SELECT  rn,col,val,sortOrder
select  row_number() Over(Order By Prod_GRP, CaseType) as rn, 
Cast(PROD_GRP as nvarchar(50)) PROD_GRP ,	Cast(CaseType as nvarchar(50)) CaseType
,Cast(NumCO as nvarchar(50)) NumCO
,Cast(NumCC  as nvarchar(50)) NumCC
,	Cast(NumLR  as nvarchar(50)) NumLR
,	Cast(NumLC as nvarchar(50)) NumLC
,	Cast(AvgResp as nvarchar(50)) AvgResp
,	Cast(AvgRest as nvarchar(50)) AvgRest
,	Cast(BillTime as nvarchar(50)) BillTime
,	Cast(NonBillTime as nvarchar(50)) NonBillTime
,	Cast(TotalTime as nvarchar(50)) TotalTime

 from test
 ) AS src1 
CROSS APPLY (Values('PROD_GRP',PROD_GRP,1),('CaseType', CaseType,2),('NumCO',NumCO,3),	('NumCC',NumCC,4),	('NumLR',NumLR,5)
,('NumLC',NumLC,6),('AvgResp',AvgResp,7),	('AvgRest',AvgRest,8),	('BillTime',BillTime,9),	('NonBillTime',NonBillTime,10),	('TotalTime',TotalTime,11)) d(col,val,sortOrder)
SELECT col as PROD_GRP, Max([1]) as GLADIATOR1,Max([2]) as GLADIATOR2, Max([3]) as  MATRIX1,  Max([4]) as  MATRIX2
FROM (SELECT sortOrder, rn,col,val
FROM mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3], [4])) AS pvt
GROUP BY col,sortOrder
Order by sortOrder

drop table Test




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s