SQL Server 2008 - make comparison with columns dynamically

Question

I have a table in SQL Server 2008 that contains approx 50 columns. Each one of these columns corresponds to a barcode scanner; we have various serial to IP boxes around scattered around the factory. Upon receiving a scan, a small application inserts a date into the relative field.

What I am trying to do is perform a comparison on the fields within this table without having to list them all manually; ideally I want to be be able to add/remove fields from the table without having to alter the SPROCS/scripts that run on the table.

So instead of

UPDATE scans 
SET completed = 1 
WHERE (scanner_1 IS NOT NULL OR scanner_2 IS NOT NULL)

... I'm looking to attempt something along the lines of

UPDATE scans 
SET completed = 1 
WHERE (/* Something to select all columns, maybe with an exclusion */ WHERE fields IS NOT NULL)

I hope I have explained this well enough, thanks for looking everybody!

---

We have a table that lists all the scanners and com ports e.g.

id (PK)
com_port
scanner

and then a table that has a field for each scanner, the key being a barcode number that is auto inserted when a record is created (example names used as the real one are a little cryptic)

barcode_id (PK)
scanner_1
scanner_2
scanner_3

Answer 1

I would certainly agree with the comments regarding normalizing the database. You should ideally have a separate table with foreign keys linking back to an IP box and a scanner.

To assist in normalising the data you may want to look at the UNPIVOT function. E.g.

CREATE TABLE T (ID INT NOT NULL, Scanner1 DATETIME, Scanner2 DATETIME, Scanner3 DATETIME, Scanner4 DATETIME)

INSERT INTO T VALUES 
    (1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    (2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL),
    (3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, NULL),
    (4, CURRENT_TIMESTAMP, NULL, NULL, NULL),
    (5, NULL, NULL, NULL, NULL)

SELECT  ID, Scanner, ScannedDate
FROM    T
        UNPIVOT 
        (   ScannedDate 
            FOR Scanner IN ([Scanner1], [Scanner2], [Scanner3], [Scanner4])
        ) UnPvt
ORDER BY ID, Scanner

which will return something like this:

ID  Scanner     ScannedDate
-----------------------------------
1   Scanner1    May 31 2012 10:40AM
1   Scanner2    May 31 2012 10:40AM
1   Scanner3    May 31 2012 10:40AM
1   Scanner4    May 31 2012 10:40AM
2   Scanner1    May 31 2012 10:40AM
2   Scanner2    May 31 2012 10:40AM
2   Scanner3    May 31 2012 10:40AM
3   Scanner1    May 31 2012 10:40AM
3   Scanner2    May 31 2012 10:40AM
4   Scanner1    May 31 2012 10:40AM

This still has the problem of having to write out the columns manually, however you can dynamically create the UNPIVOT statement too:

DECLARE @SQL NVARCHAR(MAX) = '',
        @Columns NVARCHAR(MAX) = ''

SELECT  @Columns = @Columns + ',' + QUOTENAME(Name)
FROM    SYS.COLUMNS
WHERE   OBJECT_ID = OBJECT_ID(N'T')
AND     LEFT(Name, 7) = 'Scanner'

SET @SQL = 'SELECT  ID, Scanner, ScannedDate
            FROM    T
                    UNPIVOT 
                    (   ScannedDate 
                        FOR Scanner IN (' + STUFF(@Columns, 1, 1, '') + ')
                    ) UnPvt
            ORDER BY ID, Scanner'

DECLARE @UnPivot TABLE (ID INT, Scanner VARCHAR(50), ScannedDate DATETIME)
INSERT INTO @UnPivot
EXECUTE SP_EXECUTESQL @SQL

SELECT  *
FROM    @UnPivot

Finally, if you need to transform your normalised data back to a flat view this can still be done using the PIVOT function (This assumes @SQL and @Columns are already defined as above):

CREATE TABLE ##UnPivot (ID INT, Scanner VARCHAR(50), ScannedDate DATETIME)
INSERT INTO ##UnPivot
EXECUTE SP_EXECUTESQL @SQL

SELECT  *
FROM    ##UnPivot

SET @SQL = 'SELECT  ID' + @Columns + '
            FROM    ##Unpivot
                    PIVOT 
                    (   MAX(ScannedDate)
                        FOR Scanner IN (' + STUFF(@Columns, 1, 1, '') + ')
                    ) UnPvt
            ORDER BY ID'

EXECUTE SP_EXECUTESQL @SQL

DROP TABLE ##UnPivot

I've put a full working example of dynamically unpivoting the data, then pivoting it again on SQL Fiddle

---

EDIT

I have just realised that I didn't actually answer the original question, I just advised on how to normalise the data. Here is how you could do it without manually typing out the columns:

DECLARE @SQL NVARCHAR(MAX) = '',
        @Columns NVARCHAR(MAX) = ' WHERE 1 = 1 '

SELECT  @Columns = @Columns + 'AND ' + QUOTENAME(Name) + ' IS NOT NULL '
FROM    SYS.COLUMNS
WHERE   OBJECT_ID = OBJECT_ID(N'T')
AND     LEFT(Name, 7) = 'Scanner'

SET @SQL = 'UPDATE T SET Completed = 1' + @Columns
EXECUTE SP_EXECUTESQL @SQL

Again, there is a working example on SQL Fiddle