Merging of Rows

I got a question two days back on my facebook account "Rahul Vairagi" in my DW group "SQL Server & Data warehousing Experts". The question was like this:

-------------------------------------------------------hi friend i have a small doubt in sql plz tell me 

how to implement the query

table data contain like

...

UK, id , name , sal , deptno
1, 1 , a , null , null
1, null, null , 100 , 10
2, 2 , b , null , null
2, null , null , 200 , 20
3, 3 , c , null , null
3, null , null , 300 , 30

but i want output like

id, name ,sal ,deptno
1 , a , 100 ,10
2, b ,200 ,20
3 , c ,300 ,30

and i am trying but not getting desired output .plz tell me where should be the query"

-------------------------------------------------------------------------

I tried a lot came-up with a solution

• SELECT distinct t.[UK],
  (
  SELECT top 1 t1.Id
  FROM [sample] t1
  WHERE t1.UK = t.[UK] AND t1.UK IS NOT NULL
  
  ) ID,
  
  (
  SELECT top 1 t1.name
  FROM [sample] t1
  WHERE t1.UK = t.[UK] AND t1.name IS NOT NULL
  
  ) name,
  
  (
  SELECT top 1 t2.sal
  FROM [sample] t2
  WHERE t2.[UK] = t.[UK] AND t2.sal IS NOT NULL
  ) Salary,
  
  (
  SELECT top 1 t2.deptno
  FROM [sample] t2
  WHERE t2.[UK] = t.[UK] AND t2.deptno IS NOT NULL
  --LIMIT 1
  ) deptnum
  
  FROM [sample] t
  GROUP BY t.[UK]

Multiple Result Sets in SSRS

Using SQL Server Reporting Services 2008R2 and AdventureWorks2008R2. I built a stored procedure that returns all products in a category, then a count of the number of products in the category, and the count of the number of products in that category in a specific color.

ALTER PROCEDURE [dbo].[ProductCountByCatColor]

    @Category varchar(25),

    @Color varchar(25)

AS

BEGIN

    SET NOCOUNT ON;

    SELECT PC.Name AS Category,

        PROD.ProductNumber,

        PROD.Name,

        ISNULL(PROD.Color, 'N/A') AS Color,

        ISNULL(PROD.Size, 'N/A') AS Size,

        ISNULL(PROD.[Weight], 0) AS Weight

    FROM Production.Product PROD

        INNER JOIN Production.ProductSubcategory PS ON PS.ProductSubcategoryID = PROD.ProductSubcategoryID

        INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID

    WHERE PC.Name IN (@Category)

    ORDER BY Category

    SELECT PC.Name AS Category,

        COUNT(PROD.ProductNumber) as ProdCount

    FROM Production.Product PROD

        INNER JOIN Production.ProductSubcategory PS ON PS.ProductSubcategoryID = PROD.ProductSubcategoryID

        INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID

    WHERE PC.Name IN (@Category)

    GROUP BY PC.Name

    ORDER BY PC.Name

    SELECT PC.Name AS Category,

        COUNT(PROD.ProductNumber) as ProdCount

    FROM Production.Product PROD

        INNER JOIN Production.ProductSubcategory PS ON PS.ProductSubcategoryID = PROD.ProductSubcategoryID

        INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID

    WHERE PC.Name IN (@Category)

        AND ISNULL(PROD.Color, 'N/A') IN (@Color)

    GROUP BY PC.Name

    ORDER BY PC.Name

END

When I execute the SP in SSMS, I get three result sets.


Now, I set up my report to use a stored procedure in the dataset.


I don’t even need to run the query to notice that the dataset only shows fields from the first result set.


When I run the report, I only see one set of results – the first listed in my stored procedure.

Why? The short answer is: it’s designed that way. According to BOL, “Multiple results sets from a single query are not supported.” (http://msdn.microsoft.com/en-us/library/dd239379.aspx)

How can you work around this?

If each result set is a separate query, you can put each query in a separate stored procedure, or dataset.

If the result sets need to tie together, you’ll need to work with the T-SQL to make it one result set. That may require temp tables, UNIONs, or another solution.

http://blogs.lessthandot.com/index.php/DataMgmt/ssrs/t-sql-tuesday-024-reporting 

Common Table Expression (CTE)

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

·         Create a recursive query. 

·         Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

·         Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

·         Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE

A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;

E.g.

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)                                  

AS

(

    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear

    FROM Sales.SalesOrderHeader

    WHERE SalesPersonID IS NOT NULL

)

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear

FROM Sales_CTE

GROUP BY SalesYear, SalesPersonID

ORDER BY SalesPersonID, SalesYear;

GO

Insert Data on Identity Column

After lots of  this and that, finally i got solution to insert data on Identity Column with starting with last identity_number.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DuplicateTable](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[ContactID] [int] NOT NULL,
[ManagerID] [int] NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[DuplicateTable] VALUES (1209, 16, N'M', N'M', 21, 30)
INSERT [dbo].[DuplicateTable] VALUES (1030, 6, N'S', N'M', 42, 41)
INSERT [dbo].[DuplicateTable] VALUES (1002, 12, N'M', N'M', 2, 21)
INSERT [dbo].[DuplicateTable] VALUES (1290, 3, N'S', N'M', 48, 80)
INSERT [dbo].[DuplicateTable] VALUES (1009, 263, N'M', N'M', 9, 24)
INSERT [dbo].[DuplicateTable] VALUES (1028, 109, N'S', N'M', 40, 40)
INSERT [dbo].[DuplicateTable] VALUES (1070, 21, N'S', N'F', 82, 61)
INSERT [dbo].[DuplicateTable] VALUES (1071, 185, N'M', N'F', 83, 61)
INSERT [dbo].[DuplicateTable] VALUES (1005, 3, N'M', N'F', 5, 22)
INSERT [dbo].[DuplicateTable] VALUES (1076, 185, N'S', N'M', 88, 64)


Select * from DuplicateTable

O/p
---------------------------------------------------------------------------------------------------------------------------------------

EmployeeID ContactID ManagerID MaritalStatus Gender VacationHours SickLeaveHours

---------------------------------------------------------------------------------------------------------------------------------------
1 1209 16 M M 21 30
2 1030 6 S M 42 41
3 1002 12 M M 2 21
4 1290 3 S M 48 80
5 1009 263 M M 9 24
6 1028 109 S M 40 40
7 1070 21 S F 82 61
8 1071 185 M F 83 61
9 1005 3 M F 5 22
10 1076 185 S M 88 64

Delete from DuplicateTable where EmployeeID =10

Select * from DuplicateTable

O/p
---------------------------------------------------------------------------------------------------------------------------------------
EmployeeID ContactID ManagerID MaritalStatus Gender VacationHours SickLeaveHours
---------------------------------------------------------------------------------------------------------------------------------------
1 1209 16 M M 21 30
2 1030 6 S M 42 41
3 1002 12 M M 2 21
4 1290 3 S M 48 80
5 1009 263 M M 9 24
6 1028 109 S M 40 40
7 1070 21 S F 82 61
8 1071 185 M F 83 61
9 1005 3 M F 5 22


INSERT [dbo].[DuplicateTable] VALUES (1129, 173, N'M', N'M', 41, 40)


Select * from DuplicateTable

O/p
------------------------------------------------------------------------------------------

EmployeeID ContactID ManagerID MaritalStatus Gender VacationHours SickLeaveHours
---------------------------------------------------------------------------------------------------------------------------------------
1 1209 16 M M 21 30
2 1030 6 S M 42 41
3 1002 12 M M 2 21
4 1290 3 S M 48 80
5 1009 263 M M 9 24
6 1028 109 S M 40 40
7 1070 21 S F 82 61
8 1071 185 M F 83 61
9 1005 3 M F 5 22
11 1129 173 M M 41 40

Delete from DuplicateTable where EmployeeID =11

DBCC CHECKIDENT (DuplicateTable, reseed, 9)

INSERT [dbo].[DuplicateTable] VALUES (1129, 173, N'M', N'M', 41, 40)


Select * from DuplicateTable
o/p
---------------------------------------------------------------------------------------------------------------------------------------

EmployeeID ContactID ManagerID MaritalStatus Gender VacationHours SickLeaveHours
---------------------------------------------------------------------------------------------------------------------------------------
1 1209 16 M M 21 30
2 1030 6 S M 42 41
3 1002 12 M M 2 21
4 1290 3 S M 48 80
5 1009 263 M M 9 24
6 1028 109 S M 40 40
7 1070 21 S F 82 61
8 1071 185 M F 83 61
9 1005 3 M F 5 22
10 1129 173 M M 41 40

You got it...!!!!

Identity column with decimal datatype

 "Is decimal point allowed in the identity column?" .

The identity column can never have any decimal points. It is a whole number. The decimal datatype can be used to have a bigger number in the identity column as decimal can have maximum of 38 digits.

The following are the code to create an identity column with decimal datatype

1 Specify scale as 0

           declare @t table(i decimal(38,0) identity(1,1))
           insert into @t default values
           select * from @t

2 Omit scale part

        declare @t table(i decimal(38) identity(1,1))
        insert into @t default values
        select * from @t

Exploring SSMS - Hiding System databases

 In this post we will see how to know to hide system databases from the SQL Server management studio.

In the Tool bar, goto Tools-->Options. Then Under Environment click on Startup. On the right of the windows Check the option labelled as Hide system objects in Object Explorer. If you want to bring them back to object explorer, follow the same procedure and uncheck that option

Note: You have to restart the SSMS to see the changes.

Storage of table variable

Table variables are stored in the tempdb database for execution scope only. 
Let us run this code


declare @t table(i int)
select * from tempdb.INFORMATION_SCHEMA.TABLES  

The result is


TABLE_CATALOG       TABLE_SCHEMA   TABLE_NAME       TABLE_TYPE
tempdb               dbo           #BAB3F665        BASE TABLE

If your server does not use any temporary tables, you will get the above result only. As you see a table variable is stored in the tempdb database for the execution scope only just like a temporary table. The name is prefixed by #. But unlike a temporary table, the name does not contain underscores. Becauase they are stored in execution scope only, you cannot query the INFORMATION_SCHEMA.TABLES alone to get the table variable information. The following code will not show you any table vaiables


select * from tempdb.INFORMATION_SCHEMA.TABLES

Why does SQL Server allow only one NULL value on unique constraint?

Run the following code

declare @t table(i int unique)
insert into @t
select 1 union all
select null union all
select null

You will get the following error

Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UQ__#A989971__3BD019979D26A3BB'. Cannot insert duplicate key in object'dbo.@t'. The duplicate key value is (<null></null>).
The statement has been terminated.

But other RDBMSs like ORACLE, MySQL, etc will allow multiple NULL values on a column defined as Unique key. It is because that NULL values cannot be eaqual to each other

Why does SQL Server allow only one NULL value on a column that has unique constraint?