Working with Multiple Values in a Column with SQL Server

"Any hard lesson that may do thee good."
-- Shakespeare, Much Ado About Nothing
Act I, Scene I, Line 272

SQL is a good tool when you have large sets of relational records--whether or not these kinds of relationships exist in the real world is not the aim of this post.  However, SQL is not very good at normalizing multiple records in a single attribute.

I know, an example would be good right about now.

As we see above Attribute 2 has three different values in it (Value 1, Value 2, and Value 3).

Say we want to do the following.

We want to take the Order File's Order Records and place the Order ID and others into the Order Table while placing the Order ID and Flags into the Order Flag Table.

Well we have a problem, how are we going to take the Flags and normalize them into the Order Flag Table?

Say the Flags are separated by spaces so Flags looks like this in an Order Record:

How would we do this in SQL?  Hmmm, well SQL is not really good at this kind of process so we need to do a kind of hack.

In SQL Server 2012 (should work in 2008 too) we can transform the Flags into XML that looks like this:

CHS
XMS
Once the data looks like this we can SQL Server's built in XML processing to create a virtual table to obtain the values from.

We can set up a test case using the following a CTE.

;WITH cte(value, id) AS (

  SELECT *

    FROM (

         VALUES

          ('HERP DREP CHS XMS', 1)

         ,('HELLO WORLD', 2)

       ) AS x(a, b)

)

SELECT *

FROM cte

Which looks like this in SQL Fiddle:

http://sqlfiddle.com/#!6/aed15/1

We see that we have two records:  ('HERP DREP CHS XMS', 1) and ('HELLO WORLD', 2).

Now lets take the Flags and transform them using an XML trick.


;WITH cte(value, id) AS (
  SELECT *
    FROM (
         VALUES
          ('HERP DREP CHS XMS', 1)
         ,('HELLO WORLD', 2)
       ) AS x(a, b)
)
SELECT
  flags.flag.value('.', 'varchar(50)') AS Flag
 ,id AS "Order ID"
 ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS "KEY"
FROM (
  SELECT
     CAST('<flag>' + REPLACE(value, ' ', '</flag><flag>') + '</flag>' AS XML)
    ,id
  FROM cte
) AS f(x, id)
CROSS APPLY f.x.nodes('flag') AS flags(flag)


We now have this in SQL Fiddle.

http://sqlfiddle.com/#!6/aed15/2

This gives us each Flag with its Order ID (Herp with 1, Derp with 1, CHS with 1, XMS with 1, Hello with 2, and World with 2).

This is almost what we want but we need an unique Key for each Flag across all of the records.  Now in the ETL tool I was using when I faced this problem this week, it needed an unique Key for each of the Flag records that would come out of the SQL used, even though it would be mapping this unique Key to an Identity column in SQL Server.  Yes, this is very dumb but

When tools do dumb things, one must make sure that one does not do dumb things himself. 
-- Me, this blog post

With this in mind, let us create an unique Key in a smart way for each Flag record coming out of our SQL hack.

This gives us the following in SQL Fiddle.

http://sqlfiddle.com/#!6/aed15/3

Now we have an unique Key!

This gives us each Flag with its Order ID and Key (Herp with 1 and 1, Derp with 1 and 2, CHS with 1 and 3, XMS with 1 and 4, Hello with 2 and 5, and World with 2 and 6).

Nice now we can get back to delivering real value to our customers and not just figuring out tools and strange data.

If you want to see other examples using CTEs and SELECT NULL see these other post by me.
http://comp-phil.blogspot.com/2013/02/the-case-of-not-having-unique-identity.html
http://comp-phil.blogspot.com/2013/02/how-to-remove-duplicate-records-using.html

On COUNT(*)

"The discovery of truth is prevented more effectively, not by the false appearance things present and which mislead into error, not directly by weakness of the reasoning powers, but by preconceived opinion, by prejudice."
-- Arthur Schopenhauer

I made a discovery today, much to my chagrin it seems that COUNT returns 0 only when it is presented an empty table, not when it is presented an empty group.  It seems that I am not the first to make this discovery and I do not think I will be the last.

Here are some examples to illustrate what I am talking about:

SELECT *
  FROM dbo.tbl
;

(empty)

SELECT COUNT(*)
  FROM dbo.tbl
;

COLUMN_0
0

SELECT COUNT(*)
  FROM dbo.tbl
  GROUP BY value
;

(empty)

SELECT COUNT(*) OVER (PARTITION BY value)
  FROM dbo.tbl
;

(empty)

SELECT COUNT(value)
  FROM dbo.tbl
;

COLUMN_0
0

SELECT COUNT(value)
  FROM dbo.tbl
  GROUP BY value
;


(empty)

The above can be found on SQL Fiddle.

How to Remove Duplicate Records using Windowing Functions

"Making duplicate copies and computer printouts of things no one wanted even one of in the first place is giving America a new sense of purpose. "
-- Andy Rooney

"Or the Department of Education and another ministry were worried about duplication of effort, so what did they do? They set up two committees to look into duplication and neither knew what the other was up to. It really is a world beyond parody."
-- Rory Bremner

It happens every now and then, you create a large data set for some type of testing and since you are trying to "go faster" you do not bother with automating the process and some how one of the data set insertions gets ran twice.  Now you have duplicate data in your data set and need to get rid of it.

Say you have the following table.

CREATE TABLE dbo.PurchaseOrderDetail
(
    PurchaseOrderID int NOT NULL,
    LineNumber smallint NOT NULL,
    ProductID int NULL,
    UnitPrice money NULL,
    OrderQty smallint NULL,
    rowguid uniqueidentifier ROWGUIDCOL  NOT NULL
        CONSTRAINT DF_PurchaseOrderDetail_rowguid DEFAULT (newid())
);

And the following INSERT statement is ran.

INSERT INTO dbo.PurchaseOrderDetail
(
  PurchaseOrderID,
  LineNumber,
  ProductID,
  UnitPrice,
  OrderQty
)
VALUES
 (100, 1, 1, 12.99, 1)
,(100, 2, 3, 15.99, 1)
,(100, 3, 6, 10.99, 2)
,(101, 2, 1, 12.99, 2)
,(101, 3, 2, 11.99, 1)
,(101, 4, 6, 10.99, 5)
,(102, 1, 1, 12.99, 8)
,(103, 1, 4, 19.99, 1);

Giving you the following data set.

SELECT * FROM dbo.PurchaseOrderDetail
;

PURCHASEORDERID	LINENUMBER	PRODUCTID	UNITPRICE	ORDERQTY	ROWGUID
100	1	1	12.99	1	A89225C8-3267-491C-B4E1-8688B902BDD3
100	2	3	15.99	1	528F7FC0-5870-47C7-ACD3-3955424DD742
100	3	6	10.99	2	498A1A36-8698-4374-9861-49DCF6514CF5
101	2	1	12.99	2	59820CAF-9209-4EAD-BDF0-D36170084E3C
101	3	2	11.99	1	A2973E38-B0CF-4D98-9ADC-5E26BCDDEE30
101	4	6	10.99	5	B4021C5F-0FB3-4330-B1FC-AD7C3DE6A936
102	1	1	12.99	8	A24CFF12-F402-488C-9B26-8D2AF0710F36
103	1	4	19.99	1	3044E8AC-027A-4B13-9791-40768F2A4F3E

Now say some bozo runs the insert statement again and you now have the following data set.

PURCHASEORDERID	LINENUMBER	PRODUCTID	UNITPRICE	ORDERQTY	ROWGUID
100	1	1	12.99	1	A89225C8-3267-491C-B4E1-8688B902BDD3
100	2	3	15.99	1	528F7FC0-5870-47C7-ACD3-3955424DD742
100	3	6	10.99	2	498A1A36-8698-4374-9861-49DCF6514CF5
101	2	1	12.99	2	59820CAF-9209-4EAD-BDF0-D36170084E3C
101	3	2	11.99	1	A2973E38-B0CF-4D98-9ADC-5E26BCDDEE30
101	4	6	10.99	5	B4021C5F-0FB3-4330-B1FC-AD7C3DE6A936
102	1	1	12.99	8	A24CFF12-F402-488C-9B26-8D2AF0710F36
103	1	4	19.99	1	3044E8AC-027A-4B13-9791-40768F2A4F3E
100	1	1	12.99	1	6D0193CD-198F-4A39-991C-9C1CBED4B7A5
100	2	3	15.99	1	5A007E16-76B3-425A-963E-CD1EB3A8A3D4
100	3	6	10.99	2	C01CCEBC-7FDB-4166-8780-6172015949AD
101	2	1	12.99	2	24816420-629B-42D4-94EE-D05D922EF553
101	3	2	11.99	1	8605E6A8-9CF4-4575-B8F4-F68E91D793C5
101	4	6	10.99	5	BD54EB82-4A20-484D-A5CF-53DCB9A27CB2
102	1	1	12.99	8	03DE0A36-6295-46E6-8F4E-C67E36EC2FAE
103	1	4	19.99	1	AE017968-1480-49E6-A9B7-510251C8E33A

The duplicate records have been highlighted.  Since the GUID is created a new every time it is ran, the GUID values are different in the duplicate records, but the all the other data is exactly the same.

Say that it takes a rather long time to create the and load the test data, so a TRUNCATE or DROP and recreate are out of the question.  What can we do to remove the duplicate data leaving the original data intact?

What we need is to be able to assign an identifier to each row that would be the antonym for the duplicate rows.  ROW_NUMBER with a PARTITION BY natural keys (in this example PurchaseOrderID and LineNumber) would server just this case (since we do not care about the ORDER we can use the SELECT NULL trick).  One of the records would have the row number of 1 and the duplicate would have the row number of 2, then we could simply DELETE all the records with the row number of 2.

For identifying the records we can create the following common table expression.

WITH cte AS (
  SELECT
    PurchaseOrderID
   ,LineNumber
   ,ROW_NUMBER()
      OVER (PARTITION BY PurchaseOrderID, LineNumber ORDER BY (SELECT NULL)) AS num
  FROM dbo.PurchaseOrderDetail
)

Which would give use the following (using SELECT * FROM cte)

PURCHASEORDERID	LINENUMBER	NUM
100	1	1
100	1	2
100	2	1
100	2	2
100	3	1
100	3	2
101	2	1
101	2	2
101	3	1
101	3	2
101	4	1
101	4	2
102	1	1
102	1	2
103	1	1
103	1	2

Now we have the duplicate records tagged with a row number of 2, so we can just DELETE those records.

WITH cte AS (
  SELECT
    PurchaseOrderID
   ,LineNumber
   ,ROW_NUMBER()
      OVER (PARTITION BY PurchaseOrderID, LineNumber
                   ORDER BY (SELECT NULL)) AS num
  FROM dbo.PurchaseOrderDetail
)
DELETE cte
  WHERE num > 1
;

Bingo!  We now have a clean data set once again.

SELECT * FROM dbo.PurchaseOrderDetail
;

PURCHASEORDERID	LINENUMBER	PRODUCTID	UNITPRICE	ORDERQTY	ROWGUID
100	1	1	12.99	1	A89225C8-3267-491C-B4E1-8688B902BDD3
100	3	6	10.99	2	498A1A36-8698-4374-9861-49DCF6514CF5
101	3	2	11.99	1	A2973E38-B0CF-4D98-9ADC-5E26BCDDEE30
102	1	1	12.99	8	A24CFF12-F402-488C-9B26-8D2AF0710F36
100	2	3	15.99	1	5A007E16-76B3-425A-963E-CD1EB3A8A3D4
101	2	1	12.99	2	24816420-629B-42D4-94EE-D05D922EF553
101	4	6	10.99	5	BD54EB82-4A20-484D-A5CF-53DCB9A27CB2
103	1	4	19.99	1	AE017968-1480-49E6-A9B7-510251C8E33A

To design and test out this example I used SQL Fiddle using SQL Server 2012.

You can check out all the code at the following URL: http://sqlfiddle.com/#!6/b2f21/8/5

The Case of Not Having an Unique Identity

"Always remember that you're unique. Just like everyone else."
-- Alison Boulter

Every now and then it happens, you get a data set that has been corrupted and does not have a unique identity column.  What to do?  Well you can copy the data into a temp table that has an identity, truncate the table that it is in, and then copy the data from the temp table back into the original table.  This will work but it would take a long time to execute on a large data set and it assumes you can run truncate (otherwise you would have to run delete and fill the transaction log).

Your other option would be to come up with a statement that would assign a unique identity to each row and then use this to update the table.  Luckily the windowing function ROW_NUMBER can be use for just this task.

Say you have the following table:


CREATE TABLE dbo.PurchaseOrderDetail
(
    PurchaseOrderID int NOT NULL,
    LineNumber smallint NOT NULL,
    ProductID int NULL,
    UnitPrice money NULL,
    OrderQty smallint NULL
);


And the following data:


INSERT INTO dbo.PurchaseOrderDetail
(
    PurchaseOrderID,
    LineNumber,
    ProductID,
    UnitPrice,
    OrderQty
)
VALUES (1, 110, 201, 10.99, 3),
       (1, 112, 2, 5.98, 1),
       (1, 113, 21, 11.99, 2),
       (1, 220, 1, 1.99, 5),
       (1, 210, 1, 1.99, 6),
       (1, 410, 201, 10.99, 7),
       (1, 50, 201, 10.99, 31),
       (1, 500, 301, 99.99, 1),
       (1, 116, 21, 11.99, 1),
       (1, 100, 2, 5.98, 1),
       (1, 10, 201, 10.99, 1);


As you can see there is an issue, the PurchaseOrderID has been corrupted.  Its value is always 1.  It should have a unique identity.  Let's use the ROW_NUMBER function in a CTE to give us an unique identity for each row.


WITH cte
AS
(
  SELECT
     PurchaseOrderID
    ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
    FROM dbo.PurchaseOrderDetail
)


We use a little trick of ORDERing BY SELECT NULL since we do not care about order in which we assign the row numbers and we need to have an ORDER BY for ROW_NUMBER.

Now all we need to do is use this CTE in an UPDATE to change the PurchaseOrderID to ROW_NUMBER value.


WITH cte
AS
(
  SELECT
     PurchaseOrderID
    ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
    FROM dbo.PurchaseOrderDetail
)
UPDATE cte
  SET PurchaseOrderID = num;


With this one simple statement we have just assigned a unique identity to the PurchaseOrderID.  Now quickly ALTER the table and make it a primary key, so this does not happen again!

This whole solution can be found on SQL Fiddle here.

Finding Islands of Data

"But the universe, as a collection of finite things, presents itself as a kind of island situated in a pure vacuity to which time, regarded as a series of mutually exclusive moments, is nothing and does nothing."

-- Muhammad Iqbal

When I first started using SQL Server 2008 one of the most interesting things I stumbled across was the OVER clause.  At first when I started using I enjoyed that I could use aggregate functions without the need for a GROUP BY clause, in fact I aggregate over different sets of data in the same SELECT statement!

Having grown more use to the idea of thinking in sets, I now see OVER clause and windowing functions in general are very valuable tools in the SQL tool belt.

Using the following:

CREATE TABLE #t1 (
  col1 int NOT NULL
);
GO

INSERT INTO #t1
  VALUES (2),(3),(4),(9),(11),(12),(13),(14),(22),(23),(45),(46);
GO

We create a table with one column and some integer values.

With just this simple data set we can demonstrate how islands of day can be found.  What is an island of data?  An island of data is simply a gap in the data.  Say you get a data feed of security prices, but some of the data is incomplete for some days (maybe a security is thinly traded or maybe whomever you are getting the feed from has some issues).  These gaps in the data form what is called data islands.

Looking at our simple data we can see a few data islands.

col1
2
3
4
9
11
12
13
14
22
23
45
46

The different data islands have been highlighted in different colors.  It would be nice if the data was a way that the different colors had different group identifiers so that we do a GROUP BY (or something similar) on the identifier.  As Polya shows us in How to Solve It, if we can solve an auxiliary problem perhaps we can solve this one.

If we use the ROW_NUMBER window function and ORDER BY the value in col1 we would get the following.

SELECT col1, ROW_NUMBER() OVER(ORDER BY col1) AS rowid
  FROM #t1;
GO

col1 rowid
2 1
3 2
4 3
9 4
11 5
12 6
13 7
14 8
22 9
23 10
45 11
46 12

All we did was simply return the ROW_NUMBER assigning the number based on the values in col1.  Thus 2 is assigned 1 since it is the lowest value, 9 is assigned 4 since there are three values lower than it, 46 is assigned 12 since all eleven other values are lower than it, and so on.  Each ROW_NUMBER is assigned in relation to all the other values it is based on, in this case the number is assigned based on the value in col1.

If we look closer we will find that the difference between the rowid and col1 holds constant for the whole group meaning that we can simple subtract the rowid and col1 to get a value that we can GROUP BY as an identifier.

SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS groupid
  FROM #t1;
GO

col1 groupid
2 1
3 1
4 1
9 5
11 6
12 6
13 6
14 6
22 13
23 13
45 34
46 34

Now we have our identifier to GROUP BY we can do the following to get the range of each island of data.

SELECT DISTINCT
   MIN(col1) OVER(PARTITION BY groupid) AS start_range
  ,MAX(col1) OVER(PARTITION BY groupid) AS end_range
  FROM (
    SELECT 
       col1
      ,col1 - ROW_NUMBER() OVER(ORDER BY col1) AS groupid
      FROM #t1
  ) AS x;
GO

start_range	end_range
2	4
9	9
11	14
22	23
45	46

The PARTITION BY in the OVER clause is very similar to a GROUP BY, the difference being that the PARTITION BY takes the value in the current row and looks at all the related values in the current window, or simply in the case where col1 is equal to 3 the MIN ans MAX are found for all of the col1's with the related groupid of 1, which would be the value 2 ans 4.  Since the OVER clause is looked at for each row of data we need to use a DISTINCT to remove the duplicates.

There you have it, you can find islands of data by using simple windowing functions.

I've placed this code on SQL Fiddle here.