CODEWITHSUNDEEP
sqlsql-server
Arsalan Qaiser
Arsalan Qaiser

Reputation: 457

Convert multiples row into one rows based on master key

I have following records on my table. I want to convert these records into one single row based on master key. 

records:-
id     date        userkey workinghours  masterkey
1941   2016-06-01    1         6           NULL
1946   2016-06-08    1         1           NULL
1947   2016-06-09    1         6           1946
1948   2016-06-10    1         3           1946

I want output like this.

id     startdate   endate     userkey workinghours  masterkey
1941   2016-06-01  2016-06-01    1      6            NULL
1946   2016-06-08  2016-06-10    1      10           1946

How can i do this ? As a newbie I need your help to write this query.

Upvotes: 0

Views: 88

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82524

Here is one way to do it, using ISNULL and GROUP BY:

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T as TABLE
(
    id int,
    [date] date,
    userkey int,
    workinghours int, 
    masterkey int
)

INSERT INTO @T VALUES
(1941, '2016-06-01', 1, 6, NULL),
(1946, '2016-06-08', 1, 1, NULL),
(1947, '2016-06-09', 1, 6, 1946),
(1948, '2016-06-10', 1, 3, 1946)

The query:

SELECT  ISNULL(MasterKey, id) as id,
        MIN([date]) as startdate,
        MAX([date]) as enddate,
        userkey,
        SUM(workinghours) as workinghours,
        MIN(masterKey) as masterKey
FROM @T  
GROUP BY ISNULL(MasterKey, id), userkey

Result:

id          startdate  enddate    userkey     workinghours masterKey
----------- ---------- ---------- ----------- ------------ -----------
1941        2016-06-01 2016-06-01 1           6            NULL
1946        2016-06-08 2016-06-10 1           10           1946

Upvotes: 2

Ket
Ket

Reputation: 81

This should work:

create table #tbl
(id     INT
, dt date        
, userkey INT
, workinghours  INT 
, masterkey INT
)

INSERT INTO #tbl VALUES
(1941   ,'2016-06-01'  ,  1         ,6           ,NULL)
,(1946   ,'2016-06-08'    ,1         ,1           ,NULL)
,(1947   ,'2016-06-09'    ,1         ,6           ,1946)
,(1948   ,'2016-06-10'    ,1         ,3           ,1946)

WITH WORKHOURS_CTE (
    ID
    ,origdate
    ,userkey
    ,workinghours
    ,masterkey
    )
AS
    -- Define the CTE query.  
    (
    SELECT CASE WHEN masterkey IS NULL THEN id ELSE masterkey END AS ID
        ,dt
        ,userkey
        ,workinghours
        ,masterkey
    FROM #tbl
    )
-- Define the outer query referencing the CTE name.  
SELECT ID
    ,MIN(origdate) AS STARTDATE
    ,MAX(origdate) AS ENDDATE
    ,SUM(workinghours) AS workinghours
    ,userkey
    ,MAX(masterkey) AS masterkey
FROM WORKHOURS_CTE
GROUP BY ID
    ,userkey;

Upvotes: 0

neer
neer

Reputation: 4092

Try this

DECLARE @T as TABLE
(
    id int,
    [date] date,
    userkey int,
    workinghours int, 
    masterkey int
)

INSERT INTO @T VALUES
(1941, '2016-06-01', 1, 6, NULL),
(1946, '2016-06-08', 1, 1, NULL),
(1947, '2016-06-09', 1, 6, 1946),
(1948, '2016-06-10', 1, 3, 1946)


SELECT
    MIN(ISNULL(masterkey,id)) as Id, 
    MIN([date]) as StartDate, 
    MAX([date]) as EndDate, 
    min(userkey) As Userkey, 
    sum(workinghours) As WorkingHours, 
    MIN(MasterKey) AS MasterKey
FROM
(
    SELECT  
        id ,
        date ,
        userkey ,
        workinghours ,
        masterkey,
        ISNULL(masterkey, id) TmpMasterkey
    FROM @T     
) A
GROUP BY TmpMasterkey

OUTPUT

Id      StartDate   EndDate     Userkey WorkingHours    MasterKey
1941    2016-06-01  2016-06-01  1       6               NULL
1946    2016-06-08  2016-06-10  1       10              1946

Upvotes: 0

Related Questions