Overblog Follow this blog
Administration Create my blog
Balavardhan Reddy Narani

Case Statement Tricks

April 10 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

The CASE statement is a very flexible tool. Here are just a few of the tricks you can work with it.

Item translation

One of the simplest things you can do is to derive a columns contents based on it’s contents, or the contents of another data item.

SELECT  pub_name,
        CASE WHEN state is NULL or state = ''
            THEN 'Not supplied'
            ELSE state
        END
FROM publishers

SELECT CASE WHEN datepart (dw, getdate()) In (1, 7)
            THEN 'Weekend overtime rates apply :-)'
            ELSE 'Standard rates apply'
END

Titling subtotals

You can use CUBE and ROLLUP to produce totals and subtotals within a result set, but because SQL Server places NULLS for non-totaled columns in the total rows, they do not always look very pretty. In this example we use the CASE statement to place emphasis on the subtotal lines.

SELECT   CASE GROUPING(stor_name)
                    WHEN 1 then '[State total]'
                    ELSE stor_name
                End as store,
                state,
                sum(qty)
                FROM stores s
JOIN      sales sl on s.stor_id = sl.stor_id
GROUP  BY stor_name, state
WITH     CUBE
HAVING GROUPING(state) = 0
ORDER   BY state, GROUPING(stor_name)

Simplifying Output

This batch lists of sales by store, and gives a general indication of the stores performance against the average quantity of sales per store.

declare @storeAvg int
select @storeAvg = avg(storeTotal)
from (
    select stor_id,
    sum(qty) as storeTotal
    from sales
    group by stor_id
) as derived


select  stor_name,
        storeTotal,
case    when storeTotal > @storeAvg * 1.1
            then 'Above average'
        when storeTotal < @storeAvg * 0.9
            then 'Below average'
        else 'About average'
end as storePerformance
from (
        select  stor_id,
                sum(qty) as storeTotal
                from     sales
                group    by stor_id
) as derivedStores
join stores
on stores.stor_id = derivedStores.stor_id

This query batch starts by calculating the average of total sales per store, which is stored in the variable @storeAvg, then that average is used to rate the stores sales performance.

Grouping data into ranges

This example calculates the number of orders in certain size ranges. It also shows off the power of SQL as a set-based language: to code this as in a procedural language like VB would not be able to do this in a single statement.

SELECT    SUM (CASE when qty <= 5 then 1 else 0 end) 
            as '1_to_5',
          SUM (CASE when qty between 6 and 10 then 1 else 0 end) 
            as '6_to_10',
          SUM (CASE when qty between 11 and 20 then 1 else 0 end) 
            as '11_to_20',
          SUM (CASE when qty > 20 then 1 else 0 end) 
            as '20_plus'
FROM sales

Complex Sequencing

CASE statements can be used in an ‘ORDER BY clause to order non-sequential data. This example sorts stores by name, but puts all the stores in California at the top of the list.

SELECT   stor_name, state
FROM     stores
ORDER    BY CASE state
             WHEN 'CA' then 1
             ELSE 2
         END,
         stor_name

Read more

Concatenating Row Values in Transact-SQL

April 7 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

Concatenating Row Values in Transact-SQL

There are a number of solutions and considerable debate. How do you go about Concatenating Row values.

By 'Concatenating row values' we mean this: You have a table, view or result that looks like below......and you wish to have a resultset like the one below:

Ex:  

Table Structure :

Jobs : Job_ID, Job_Title
Employee :Emp_ID, Emp_name, Job_ID  
-- Job ID is Foreign Key 

Tables Data looks like :

Jobs

Job_ID            Job_Title
   1              Grade - A
   2              Grade - B
   3              Grade - C

Employee

Emp_ID                           Emp_name                  Job_ID
1                                 Emp1                        1 
2                                 EMP2                        1
3                                 EMP3                        1  
4                                 EMP7                        2
5                                 EMP4                        2  
6                                 EMP8                        3
7                                 EMP9                        3  
8                                 EMP5                        1


Required Result Set :

Job_ID                        Emp_Names
1                            EMP1,EMP2,EMP3,Emp5
2                            EMP7,EMP4
3                            EMP8,EMP9




The XML method

Here is a technique for string concatenation that uses the FOR XML clause with PATH mode.

SELECT  E1.Job_ID,
       ( SELECT E2.Emp_name + ','
          FROM  Employee E2
          WHERE E2.Job_ID= E1.Job_ID
          ORDER BY E2.Emp_Name
          FOR XML PATH('') ) AS EmpNames
FROM Employee E1
GROUP BY Job_ID


 

Scalar UDF with variable concatenation in SELECT

CREATE  FUNCTION udf_update_concat (@jobid INT) RETURNS VARCHAR(MAX) AS

BEGIN

DECLARE  @t TABLE(p VARCHAR(40))
DECLARE  @r VARCHAR(MAX)
SET @r = SPACE(0)

INSERT  @t ( p )
SELECT Emp_name FROM Employee WHERE Job_ID = @jobid

IF @@ROWCOUNT > 0
    UPDATE  @t

SET  @r = @r + p + ','
RETURN(@r)
END


--Here is how to use this function:

SELECT  Job_ID, dbo.udf_update_concat(Job_ID)
 FROM Employee
 GROUP BY  Job_ID


 

Read more

How to Insert Values into an Identity Column in SQL Server

April 1 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

Identity columns are commonly used as primary keys in database tables.  These columns automatically assign a value for each new row inserted.  But what if you want to insert your own value into the column?  It's actually very easy to do.  First we'll need a table to work with.  My examples will use this table:


use tempdb
GO
IF OBJECT_ID('IdentityTable') IS NOT NULL
    DROP TABLE IdentityTable
GO
CREATE TABLE IdentityTable (
               TheIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
               TheValue NVARCHAR(20) NOT NULL )
GO

Simply trying to INSERT a value into the identity column generates an error:


INSERT IdentityTable(TheIdentity, TheValue)
VALUES (1, 'First Row')
GO


Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'IdentityTable' when IDENTITY_INSERT is set to OFF.


The trick is to enable IDENTITY_INSERT for the table.  That looks like this:


SET IDENTITY_INSERT IdentityTable ON


INSERT INTO IdentityTable(TheIdentity, TheValue) VALUES (3, 'First Row')


SET IDENTITY_INSERT IdentityTable OFF


Here are some key points about IDENTITY_INSERT

    * It can only be enabled on one table at a time.  If you try to enable it on a second table while it is still enabled on a first table SQL Server will generate an error.
    * When it is enabled on a table you must specify a value for the identity column.
    * The user issuing the statement must own the object, be a system administrator (sysadmin role), be the database owner (dbo) or be a member of the db_ddladmin role in order to run the command.

If you insert a value greater than the current identity seed SQL Server uses the value to reset the identity seed. 
For example:


SET IDENTITY_INSERT IdentityTable ON

INSERT INTO IdentityTable(TheIdentity, TheValue) VALUES (10, 'Row Ten')


SET IDENTITY_INSERT IdentityTable OFF

INSERT INTO IdentityTable(TheValue) VALUES ('Should be 11')


SELECT * FROM IdentityTable

GO


(1 row(s) affected)

(1 row(s) affected)

TheIdentity TheValue
----------- --------------------
         10 Row Ten
         11 Should be 11

(2 row(s) affected)

Read more