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]

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...!!!!

Computed parameters in Stored Procedure

 You are aware of Computed Columns in SQL Server. In a similar way you can also use Computed Parameters. Consider the following procedure.

1.Create procedure test

2.(

3.@datetime datetime,

4.@date date = @datetime

5.)

6.as

7.select @datetime as date_with_time, @date as date_only

When you execute the procedure you get this result

1.EXEC '2001-10-18 12:45:22.000'

1.date_with_time          date_only

2.----------------------- ----------

3.2001-10-18 12:45:22.000 2001-10-18

As you see the parameter accepts the value supplied for @datetime and convert it to date datatype. 
When you supply value for @date, it will ignore the value of @datetime, the default value for it

view source

print?

1.EXEC '2001-10-18 12:45:22.000','2012-01-22 19:15:12.000'

1.date_with_time          date_only

2.----------------------- ----------

3.2001-10-18 12:45:22.000 2012-01-22

However it is not possible to use an expression for the computed paramters 
ie @date date=dateadd(day,10,@datetime) will not work. You should be aware of this when you use paramers
this way.