How to Flip Value of Bit Field in SQL Server

DECLARE @field1 BIT
DECLARE @field2 BIT

SET @field1 = 0
SET @field2 = 1

SELECT @field1 Field1, @field2 Field2

-- Method zero
SELECT @field1 = ~@field1, @field2 = ~@field2
SELECT @field1 Field1, @field2 Field2

-- Method one
SELECT @field1 = 1^@field1, @field2 = 1^@field2
SELECT @field1 Field1, @field2 Field2

-- Method 2
SELECT @field1 = 1-@field1, @field2 = 1-@field2
SELECT @field1 Field1, @field2 Field2

-- Method 3
SELECT @field1 = (1+@field1)%2, @field2 = (1+@field2)%2
SELECT @field1 Field1, @field2 Field2

-- Method 4
SELECT @field1 = ABS(@field1-1), @field2 = ABS(@field2-1)
SELECT @field1 Field1, @field2 Field2

Questi sono i risultati:

Field1 Field2
------ ------
0      1

Field1 Field2
------ ------
1      0

Field1 Field2
------ ------
0      1

Field1 Field2
------ ------
1      0

Field1 Field2
------ ------
0      1

Field1 Field2
------ ------
1      0

Trovato qui e qui.

SQL SERVER – Finding Max Value From Multiple Values

Here is the simple example which will work with SQL Server 2008 and later versions.

Example 1: With Variables

DECLARE @Int1 INT = 1, @Int2 INT = 3, @Int3 INT = 5;
SELECT MAX(v)
FROM (VALUES (@Int1), (@Int2), (@Int3)) AS value(v);

Example 2: With Static Values

SELECT MAX(v)
FROM (VALUES (1),(5),(3)) AS value(v);

Example 3: With Columns

CREATE TABLE SampleTable
( ID INT PRIMARY KEY ,
Int1 INT,
Int2 INT,
Int3 INT);
INSERT INTO SampleTable (ID, Int1, Int2, Int3)
VALUES (1, 1, 2, 3);
INSERT INTO SampleTable (ID, Int1, Int2, Int3)
VALUES (2, 3, 2, 1);
INSERT INTO SampleTable (ID, Int1, Int2, Int3)
VALUES (3, 1, 3, 2);

-- Query to select maximum value
SELECT ID,
(SELECT MAX(v)
FROM (VALUES (Int1), (Int2), (Int3)) AS value(v)) AS MaxValue
FROM SampleTable;

Testato su SQL 2008R2.
Trovato qui.

Create a GUID (Globally Unique Identifier) in VBA

Public Function GetGUID() As String

    GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36)

End Function

Tesato in MS Access 2003.
Trovato qui.

SQL SERVER – GROUP BY Columns with XMLPATH

Here is the script of the original table which generates a table displayed on the left side of the image.

USE tempdb
GO
CREATE TABLE StudentEnrolled (ClassID INT, FirstName VARCHAR(20), LastName VARCHAR(20))
GO
INSERT INTO StudentEnrolled (ClassID, FirstName, LastName)
SELECT 1, 'Thomas', 'Callan'
UNION ALL
SELECT 1, 'Henry', 'Quinto'
UNION ALL
SELECT 2, 'Greg', 'McCarthy'
UNION ALL
SELECT 2, 'Brad', 'Grey'
UNION ALL
SELECT 2, 'Loren', 'Oliver'
UNION ALL
SELECT 3, 'Elliot', 'Kirkland'
GO
--
SELECT *
FROM StudentEnrolled
GO

Now we can use XMLPATH to concat the firstname and lastname of the student and along with that we can also group by them using following script. Now this was just an example, but you can in future use this script for many other purposes.

SELECT
[ClassID],
STUFF((
SELECT ', ' + [FirstName] + ' ' + [LastName]
FROM StudentEnrolled
WHERE (ClassID = SE.ClassID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS FullName
FROM StudentEnrolled SE
GROUP BY ClassID
GO

Trovato qui.