Solved

Complex SQL query

Posted on 2013-12-30
3
464 Views
Last Modified: 2014-01-21
Table a:
ID SID SERVICES  PN  AU
1  100   AAA     0    1
1  200   BBB     1    1
1  300   CCC     0    2
2  100   AAA     1    1  

Table B:
ID  AAA BBB CCC DDD EEE FFF
1    5   2   3   4  5    6
2    9   8   7   1   8   2

Table c:

SID  NAME
100   AAA
200   BBB
300   CCC
400   DDD
500   EEE
600   FFF


Now i want to insert all values for the corresponding columns present in Table B to Table A in the following method.
Check if services is already in the table A then

if it's already then update the PN to 5 for ID 1 and SERVICES =AAA
Here resultant will be as below after the changing the value in table A.

ID SID SERVICES  PN  AU
1  100   AAA     5    1

ELse
it should insert a new record in the table A.
E.g
DDD,EEE,FFF services is not present for ID =1. So, it should insert a new record with PU as 0 and AU as the corresponding value.
So,result will be as

Table a:
ID SID SERVICES  PN  AU
1  100   AAA     0    1
1  200   BBB     1    1
1  300   CCC     0    2
1  400   DDD     0    4
1  500   EEE     0    5
1  600   FFF     0    6

Note SID will be taken from table C.

Like wise it should loop table B and fill up all missing values in the table B.
0
Comment
  • 2
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39748182
Hi.

If you are dealing with a small list of values, such as the six you listed here, you can use PIVOT and UNPIVOT to help simplify this.  In addition, for SQL 2008 and higher, you can use MERGE to handle the UPDATE or INSERT in one statement.

Here is an example, using your sample data above.
DECLARE @tableA TABLE(ID INT, [SID] INT, [SERVICES] VARCHAR(10), PN INT, AU INT, PRIMARY KEY(ID, [SID]));
INSERT INTO @tableA(ID, [SID], [SERVICES], PN, AU)
VALUES(1, 100, 'AAA', 0, 1),
      (1, 200, 'BBB', 1, 1),
      (1, 300, 'CCC', 0, 2),
      (2, 100, 'AAA', 1, 1)
;

DECLARE @tableB TABLE(ID INT, AAA INT, BBB INT, CCC INT, DDD INT, EEE INT, FFF INT, PRIMARY KEY(ID));
INSERT INTO @tableB(ID, AAA, BBB, CCC, DDD, EEE, FFF)
VALUES(1, 5, 2, 3, 4, 5, 6),
      (2, 9, 8, 7, 1, 8, 2)
;

DECLARE @tableC TABLE([SID] INT, [NAME] VARCHAR(10), PRIMARY KEY([SID]));
INSERT INTO @tableC([SID], [NAME])
VALUES(100, 'AAA'),
      (200, 'BBB'),
      (300, 'CCC'),
      (400, 'DDD'),
      (500, 'EEE'),
      (600, 'FFF')
;

/* 
UNPIVOT demonstration. 
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
*/
SELECT u.ID, c.[SID], c.[NAME], u.PN_AU
FROM @tableB b
UNPIVOT (
    PN_AU
    FOR SERVICES_NAME
    /* Requires static list of column names. */
    IN (AAA, BBB, CCC, DDD, EEE, FFF)
) u
/* SELECT, or later JOINs, use UNPIVOT/PIVOT alias. */
JOIN @tableC c ON c.[NAME] = u.SERVICES_NAME
;

Open in new window


Results:
ID          SID         NAME       PN_AU
----------- ----------- ---------- -----------
1           100         AAA        5
1           200         BBB        2
1           300         CCC        3
1           400         DDD        4
1           500         EEE        5
1           600         FFF        6
2           100         AAA        9
2           200         BBB        8
2           300         CCC        7
2           400         DDD        1
2           500         EEE        8
2           600         FFF        2

Open in new window


With the normalized data, you can now MERGE data with table A:
/*
MERGE demonstration.
http://msdn.microsoft.com/en-us/library/bb510625.aspx 
*/
MERGE @tableA a
USING (
    SELECT u.ID, c.[SID], c.[NAME], u.PN_AU
    FROM @tableB b
    UNPIVOT (
        PN_AU
        FOR SERVICES_NAME
        IN (AAA, BBB, CCC, DDD, EEE, FFF)
    ) u
    JOIN @tableC c ON c.[NAME] = u.SERVICES_NAME
) b ON b.[ID] = a.[ID] AND b.[SID] = a.[SID]
WHEN MATCHED THEN /* UPDATE PN. */
    UPDATE SET a.PN = b.PN_AU
WHEN NOT MATCHED BY TARGET THEN /* INSERT AU. */
    INSERT (ID, [SID], [SERVICES], PN, AU)
    VALUES (b.ID, b.[SID], b.[NAME], 0, b.PN_AU)
OUTPUT $action, INSERTED.[ID], INSERTED.[SID], INSERTED.[SERVICES], INSERTED.PN, INSERTED.AU
;

Open in new window


Using OUTPUT allows you to audit what changed.  For example:
$action    ID          SID         SERVICES   PN          AU
---------- ----------- ----------- ---------- ----------- -----------
UPDATE     1           100         AAA        5           1
UPDATE     1           200         BBB        2           1
UPDATE     1           300         CCC        3           2
INSERT     1           400         DDD        0           4
INSERT     1           500         EEE        0           5
INSERT     1           600         FFF        0           6
UPDATE     2           100         AAA        9           1
INSERT     2           200         BBB        0           8
INSERT     2           300         CCC        0           7
INSERT     2           400         DDD        0           1
INSERT     2           500         EEE        0           8
INSERT     2           600         FFF        0           2

Open in new window


Similarly (but in reverse), you can update table B from table A as follows.
/* MERGE back data, or INSERT where ID does not exist, to table B. */
INSERT INTO @tableB(ID, AAA, BBB, CCC, DDD, EEE, FFF)
/* 
PIVOT demonstration. 
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
*/
SELECT ID, AAA, BBB, CCC, DDD, EEE, FFF
FROM (
    SELECT [ID], [SERVICES]
         , PN_AU = COALESCE(NULLIF(PN, 0), AU)
    FROM @tableA
) a
PIVOT (
    MAX(PN_AU)
    FOR [SERVICES]
    /* Requires static list of values. */
    IN (AAA, BBB, CCC, DDD, EEE, FFF)
) p
WHERE NOT EXISTS (
    /* WHERE filters also use UNPIVOT/PIVOT alias. */
    SELECT 1 FROM @tableB b WHERE b.[ID] = p.[ID]
)
;

Open in new window


If you need additional explanations beyond my code comments, please let me know.

Best regards,

Kevin

P.S. by default UNPIVOT will exclude rows for AAA, BBB, CCC, DDD, EEE, or FFF columns of table B where the value of the column is NULL.  If table B uses 0 or other default value, you may need to ensure you check for that on matches to ensure you do not overwrite valid data with some default.  This way, your table A will be the master.  Then you can safely overwrite table B with the PIVOT of table A.  Consequently, this is why I showed the update of table B after table A.  You always can switch this based on the need of your environment and how data flows.
0
 
LVL 31

Expert Comment

by:awking00
ID: 39748196
So,result will be as
Table a:
ID SID SERVICES  PN  AU
1  100   AAA     0    1  => Shouldn't this be 1  100  AAA    5   1 in accordance with you 1st rule?
1  200   BBB     1    1
1  300   CCC     0    2
1  400   DDD     0    4
1  500   EEE     0    5
1  600   FFF     0    6

>>Like wise it should loop table B and fill up all missing values in the table B.<<
Can you explain this a little further? Perhaps what you would expect tableB to look like after the query.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39748233
@awking00, I meant to edit my comment above to include a sample of PIVOT to ensure VIVEKANANDHAN_PERIASAMY's meaning of fill up missing values in table B matches mine; therefore, thank you for the reminder.

If your sample data was like this (note extra value in table A), the corrected table B could look like the result of this code.
DECLARE @tableA TABLE(ID INT, [SID] INT, [SERVICES] VARCHAR(10), PN INT, AU INT, PRIMARY KEY(ID, [SID]));
INSERT INTO @tableA(ID, [SID], [SERVICES], PN, AU)
VALUES(1, 100, 'AAA', 0, 1),
      (1, 200, 'BBB', 1, 1),
      (1, 300, 'CCC', 0, 2),
      (2, 100, 'AAA', 1, 1),
      (3, 400, 'DDD', 0, 7)
;

DECLARE @tableB TABLE(ID INT, AAA INT, BBB INT, CCC INT, DDD INT, EEE INT, FFF INT, PRIMARY KEY(ID));
INSERT INTO @tableB(ID, AAA, BBB, CCC, DDD, EEE, FFF)
VALUES(1, 5, 2, 3, 4, 5, 6),
      (2, 9, 8, 7, 1, 8, 2)
;

DECLARE @tableC TABLE([SID] INT, [NAME] VARCHAR(10), PRIMARY KEY([SID]));
INSERT INTO @tableC([SID], [NAME])
VALUES(100, 'AAA'),
      (200, 'BBB'),
      (300, 'CCC'),
      (400, 'DDD'),
      (500, 'EEE'),
      (600, 'FFF')
;

/* other code to MERGE data from table B to table A. */

SELECT ID, AAA, BBB, CCC, DDD, EEE, FFF
FROM (
    SELECT [ID], [SERVICES]
         , PN_AU = COALESCE(NULLIF(PN, 0), AU)
    FROM @tableA
) a
PIVOT (
    MAX(PN_AU)
    FOR [SERVICES]
    /* Requires static list of values. */
    IN (AAA, BBB, CCC, DDD, EEE, FFF)
) p
;

Open in new window


Results:
ID          AAA         BBB         CCC         DDD         EEE         FFF
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           5           2           3           4           5           6
2           9           8           7           1           8           2
3           NULL        NULL        NULL        7           NULL        NULL

Open in new window


Note: PIVOT results in NULL when there is a missing record in table A for a given column of table B.  Hence, my note above about default values applies in reverse also.

Is this what you are after?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now