Category Archives: SQL Server

Converting a Vertical Table to an Horizontal Table in SQL Server

Today I’ve encountered a vertical table in an SQL Database and I wanted to transform it to an horizontal one. A vertical table is described as an EAV model.

Imagine you have this table


CREATE TABLE VerticalTable
(
Id int,
Att_Id varchar(50),
Att_Value varchar(50)
)

INSERT INTO VerticalTable
SELECT 1, 'FirstName', 'John' UNION ALL
SELECT 1, 'LastName', 'Smith' UNION ALL
SELECT 1, 'Email', 'john.smith@dummy.com' UNION ALL
SELECT 2, 'FirstName', 'Jack' UNION ALL
SELECT 2, 'LastName', 'Daniels' UNION ALL
SELECT 2, 'Email', 'jack.daniels@dummy.com'

If you run


SELECT * FROM VerticalTable

you get this

Id Att_Id Att_Value
1 1 FirstName John
2 1 LastName Smith
3 1 Email john.smith@dummy.com
4 2 FirstName Jack
5 2 LastName Daniels
6 2 Email jack.daniels@dummy.com

 

To convert this into an horizontal table I’m going to use PIVOT.


SELECT [Id], [FirstName], [LastName], [Email] 
FROM
(
 SELECT Id, Att_Id, Att_Value FROM VerticalTable
) as source
PIVOT
(
 MAX(Att_Value) FOR Att_Id IN ([FirstName], [LastName], [Email])
) as target

And I will get this

Id FirstName LastName Email
1 1 John Smith john.smith@dummy.com
2 2 Jack Daniels jack.daniels@dummy.com

 

You can find the code here.

SQL Persisted Computed Column ANSI_NULLS

I had to recreate a table in SQL Server just because it was created with ANSI_NULLS ON

Here you can check the requirements to create a persisted  computed column on SQL Server.

“…the SET options in the following table must be set to the values shown in the Required value column…”

SET options Required value Default server value Default

OLE DB and ODBC value

Default

DB-Library value

ANSI_NULLS ON OFF ON OFF
ANSI_PADDING ON ON ON OFF
ANSI_WARNINGS* ON OFF ON OFF
ARITHABORT ON ON OFF OFF
CONCAT_NULL_YIELDS_NULL ON OFF ON OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ON OFF ON OFF

 

How to Access the Previous Row and Next Row value in SELECT statement?

LAG –¬†http://msdn.microsoft.com/en-us/library/hh231256.aspx

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, 
       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

LEAD –¬†http://msdn.microsoft.com/en-us/library/hh213125.aspx

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, 
    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

SQL SERVER 2012 and 2014