Feeds:
Posts
Comments

Archive for the ‘Sql’ Category

Introduction :

Here we will see how to simply make a query in a table without worring about style case.

The solution is the KeyWord COLLATE.

As a matter of fact, if you do a query which returns all elements (by a case-unsensitive way) inside en table with a LIKE clause for example, you’ll have to use COLLATE clause. This clause must be combined with a string expression to apply a collation cast.

Here let’s choose SQL_Latin1_General_Cp437_CI_AI.
Latin1_General:  Identifying alphabet or used language for which sort are applied.
Cp437:
code page 437.
CI:
case-unsensitive.
AI:
accent unsensitive.

SELECT *
FROM TABLE
WHERE My_Field LIKE '%Calculé%'  -- means computed in French
COLLATE SQL_Latin1_General_Cp437_CI_AI;

Here’s the output :

calcule
CalCule
calculE
Calculé

Enjoy 😉

Advertisements

Read Full Post »

Scope Identity Vs @@Identity

Introduction :

In a former query I had to get the identity value of the last insertion in a database table , but I didn’t get what I was waiting for!!
To simulate the situation let’s create 2 tables :

CREATE TABLE [dbo].[TABLE_1]
(
    Id [int] IDENTITY(1,1) NOT NULL,
    name [nchar](10) NULL,
    CONSTRAINT [PK_TABLE_1] PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE [dbo].[TABLE_2]
(
    Id [int] IDENTITY(1,1) NOT NULL,
    name [nchar](10) NULL,
    CONSTRAINT [PK_TABLE_2] PRIMARY KEY CLUSTERED ([Id] ASC)
)

We will do an insertion inside TABLE_2 from a TRIGGER -> AFTER INSERT inside TABLE_1.

CREATE TRIGGER [dbo].[TR_TABLE_1] ON [dbo].[TABLE_1]
AFTER INSERT
AS
BEGIN
    INSERT INTO TABLE_2(name) VALUES ('inserted_foo')
END

We insert 4 records to get a different identity from the one of TABLE_1.
TABLE_1 has 0 record and its identity is 1 by default.
TABLE_2 has 4 records and its identity is 4.
At last we do our insertion inside TABLE_1 and we get the identity with @@identity first and scope identity().

INSERT INTO T1(name) VALUES ('foo')
PRINT @@IDENTITY
PRINT SCOPE_IDENTITY()

Result :
(1 row(s) affected)
(1 row(s) affected)
5
1

@@IDENTITY returns the last identity inserted whatever the level where this insertion was made. The SCOPE_IDENTITY () will always refer to inclusion level where this function is executed.
In conclusion, if you’re not sure of the existence of triggers underlying do prefer using SCOPE_IDENTITY() to avoid surprises.

NB : Use IDENT_CURRENT function to return the identity of the last insertion, whatever the session for a given table 🙂

SELECT IDENT_CURRENT('tableName')

Read Full Post »

Available in SQL Server 2005/2008, the Pivot relationnal operator is used to convert rows into columns data. This feature is frequently used in reports and is pretty easy to work with.

Available in SQL Server 2005/2008, the Pivot relationnal operator is used to convert rows into columns data. This feature is frequently used in reports and is pretty easy to work with.

Here is a example :
We have a table Product, if we execute this simple query :
SELECT productId,_year,amount
FROM Products

We have this result :

productId	_year	amount
124			2001	125
125			2001	454
126			2001	75
127			2002	256
128			2004	457
129			2004	585
130			2002	142
131			2002	785
132			2005	452
133			2005	864
134			2005	762
135			2004	425
136			2003	452
137			2003	1024
138			2003	575

Now we are going to use PIVOT to have the years as columns and for a productId, the sum of amounts for 2001 and 2003 :

SELECT * FROM
(
	SELECT productId,_year,amount
	FROM Products
)t
PIVOT (SUM(amount) FOR _year
IN ([2001],[2003])) AS pvt

So, we will have this result :

productId	2001	2003
124			125		NULL
125			454		NULL
126			75		NULL
127			NULL	NULL
128			NULL	NULL
129			NULL	NULL
130			NULL	NULL
131			NULL	NULL
132			NULL	NULL
133			NULL	NULL
134			NULL	NULL
135			NULL	NULL
136			NULL	452
137			NULL	1024
138			NULL	575

Ok, that’s nice. But if we consider that we don’t know the names of the columns, we have to make our PIVOT dynamic. Look at the following code :

We are first going to build a string that concatenes all years

DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT
						'],[' + ltrim(str(_year))
                        FROM    Products
                        ORDER BY '],[' + ltrim(str(YEAR(_year)))
                        FOR XML PATH('')
						), 1, 2, '') + ']'

So this string will contain all years needed for our PIVOT query:

[2001],[2002],[2003],[2004],[2005]

After that, all we have to do is to dynamically create our PIVOT query. Here is the complete query :

DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT
						'],[' + ltrim(str(_year))
                        FROM    Products
                        ORDER BY '],[' + ltrim(str(YEAR(_year)))
                        FOR XML PATH('')
						), 1, 2, '') + ']'

SET @query =
'SELECT * FROM
(
	SELECT productId,_year,amount
	FROM Products
)t
PIVOT (SUM(amount) FOR _year
IN ('+@years+')) AS pvt'

EXECUTE (@query)

And here is the displayed result :

productId	2001	2002	2003	2004	2005
124			125		NULL	NULL	NULL	NULL
125			454		NULL	NULL	NULL	NULL
126			75		NULL	NULL	NULL	NULL
127			NULL	256		NULL	NULL	NULL
128			NULL	NULL	NULL	457		NULL
129			NULL	NULL	NULL	585		NULL
130			NULL	142		NULL	NULL	NULL
131			NULL	785		NULL	NULL	NULL
132			NULL	NULL	NULL	NULL	452
133			NULL	NULL	NULL	NULL	864
134			NULL	NULL	NULL	NULL	762
135			NULL	NULL	NULL	425		NULL
136			NULL	NULL	452		NULL	NULL
137			NULL	NULL	1024	NULL	NULL
138			NULL	NULL	575		NULL	NULL

Enjoy 😉
PS : You might have this error message when you run the query :

Incorrect syntax near ‘PIVOT’. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

So, to enable this feature, you have to set the compatibility level of your database to a higher level by using the following stored procedure :

--If you are running SQL 2005 
EXEC sp_dbcmptlevel 'myDatabaseName', 90
--If you are running SQL 2008 
EXEC sp_dbcmptlevel 'myDatabaseName', 100

Read Full Post »