Microsoft MVP Award - 2017

On July 2017, I was proud to receive my second Microsoft Most Valuable Professional (MVP) award. This year the award came in earlier than expected, as the Microsoft MVP program decided to grant these awards once a year moving forward.  

This is rewarding recognition that makes all the on-going work that I do for the different software communities around the world worth it. 

I enjoy knowing that what I learn and share with others has some meaningful value.

What to Expect Next

 My plans for the next year is to continue to learn and share with others by writing blogs  entries, deliver more presentations and help on different technical forums like Stackoverflow, ASP.NET, GitHub, Twitter and CodeProject.

On the technology side,  I plan to continue the  grow on areas like Angular 2, Node.js, Azure Technologies, SQL Server, NoSQL,  ASP.Net , Visual Studio, and Microsoft Core technologies to expand my areas into the Linux world. 

Thanks again to Microsoft and those working the MVP program as well as the community that enjoy and support my contributions.

Oscar Garcia

@ozkary

Ozkary.com

Microsoft MVP

2016-2017

Originally published by ozkary.com

SQL Server - Pivot Rows to Columns

On systems that are meta-data driven, there may not be a concrete table schema declaration, and the meta-data values can be stored as rows. To provide an example, let’s build a custom_field table that can hold multiple custom values for a particular entity:

CodeProject

Pivoting rows to columns

Setup the table and sample data

create table dbo.custom_fields (    ref_id int,     field_id int,    field_name varchar(55),    field_value int ) go INSERT INTO [dbo].[custom_fields]            ([ref_id]            ,[field_id]            ,[field_name]            ,[field_value])               VALUES  (1020, 1000,'width',10) ,(1020, 1001,'height',20) ,(1020, 1003,'thick',5) ,(2010, 1000,'width',15) ,(2010, 1001,'height',5) ,(2010, 1003,'thick',5) ,(2010, 1003,'units',5) ,(3000, 1000,'width',35) ,(3000, 1001,'height',55) ,(3000, 1003,'thick',20) --returns all the records select * from custom_fields

After we create the table and insert the test data, we can run a query to select all the records. The results should as follows:

The problem with the way the data is stored is that those values are properties values for an entity associated by the ref_id, and we would want to read those values as columns instead of rows as shown below:

Pivot Query

To convert those rows to columns, we need to use the PIVOT relational operator which enables us to convert the table results into a different shape. Let’s write a SQL query that can do that for us.

SELECT ref_id,width,height,thick,units FROM (   SELECT [ref_id], [field_name], [field_value]   FROM custom_fields    ) fields pivot (   MAX(field_value)   FOR [field_name] in (width,height,thick,units) ) piv;

The syntax may not be clear at first, but the query pivots the data on the field_name (FOR Expression) values, so that they become the column heading. This matches the meta-data in the field_name column, so we can read the field value using the MAX function. 

When a field_name does not exist for a particular row, the return value is null.  For example, both ref_ids (1020, 3000) do not have a units custom field.

This should enable us to make more complex pivot queries using SQL Server.

Thanks for reading.

Originally published by ozkary.com