Transform Matrix-like Data (UNPIVOT Data With T-SQL)


--http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/6217a37e-82b5-40c3-8401-50236a328f26

CREATE TABLE [dbo].[theMatrix](
	[COL0] [varchar](10) NULL,
	[COL1] [varchar](10) NULL,
	[COL2] [varchar](10) NULL,
	[COL3] [varchar](10) NULL,
	[COL4] [varchar](10) NULL,
	[COL5] [varchar](10) NULL,
	[COL6] [varchar](10) NULL,
	[UID] [int] IDENTITY(1,1) NOT NULL,
	[col7] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[theMatrix] ON 

GO
INSERT [dbo].[theMatrix] ([COL0], [COL1], [COL2], [COL3], [COL4], [COL5], [COL6], [UID], [col7]) VALUES (NULL, N'KPI1', N'KPI1', N'KPI1', N'KPI2', N'KPI2', N'KPI2', 9, N'kk')
GO
INSERT [dbo].[theMatrix] ([COL0], [COL1], [COL2], [COL3], [COL4], [COL5], [COL6], [UID], [col7]) VALUES (NULL, N'15-May', N'16-May', N'17-May', N'15-May', N'16-May', N'17-May', 10, NULL)
GO
INSERT [dbo].[theMatrix] ([COL0], [COL1], [COL2], [COL3], [COL4], [COL5], [COL6], [UID], [col7]) VALUES (N'FL', N'3.29', N'2.43', N'3.26', N'4.34', N'7.45', N'3.53', 11, NULL)
GO
INSERT [dbo].[theMatrix] ([COL0], [COL1], [COL2], [COL3], [COL4], [COL5], [COL6], [UID], [col7]) VALUES (N'NY', N'5.43', N'9.43', N'3.24', N'2.34', N'5.43', N'7.34', 12, N'5.55')
GO
SET IDENTITY_INSERT [dbo].[theMatrix] OFF
GO


select * FROM  [dbo].[theMatrix]  

  ;with mycte as
( select * from (select *,row_number() Over(order by col0) rn   from thematrix  ) src
cross apply (
values( col0,'col0'),(col1,'col1'),(col2,'col2'),(col3,'col3')
,(col4,'col4'),(col5,'col5'),(col6,'col6'), (col7,'col7') )   d(vals,cols)
)
 

SELECT m.Col0,a.vals AS c1,b.vals AS c2,m.vals
FROM   mycte m
       LEFT JOIN (SELECT *
                  FROM   mycte
                  WHERE  rn = 1 AND COL0 IS NULL) a
              ON m.cols = a.cols
       LEFT JOIN (SELECT *
                  FROM   mycte
                  WHERE  rn = 2 AND COL0 IS NULL) b
              ON m.cols = b.cols
WHERE  m.col0 IS NOT NULL AND m.col0 <> m.vals 

Advertisements


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