CODEWITHSUNDEEP
sql-server-2014
wwidhoo
wwidhoo

Reputation: 105

whether to use pivot or non-pivot without any aggregate function

I got two tables table 1 and 2 as shown below. I need to insert the Std_rid for the row which has A and its corresponding Act_rid present in table 2 in resultant table 3. I'm only using the sample here. I'm very confused whether to use pivot or unpivot here. Any help appreciated. Thanks

  Table 1
 ----------
    G_Standard   N_Standard     Skill    One_for_all    Am_Water    B2Future    Std_rid

     ELW.6       Lit.W.K.6      Writing                   A                        1
     ELW.8       Lit.W.K.8      Writing                               A           2
     ELW.7       Lit.W.K.7      Writing        A                                   3

   Table 2
   ----------
  Act_rid     Act_Desc     date       createdby

    3         Am_Water     2/4/15     sys
    6         B2Future     2/4/15     sys
    1         One_for_all  2/14/15    sys


  Output Result Table 3
  ----------
     ID    Std_rid   Act_rid
     1        1        3
     2        2        6
     3        3        1

Upvotes: 0

Views: 18

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32190

This is an UNPIVOT. UNPIVOT turns columns into rows.

;WITH Unpivot_Table_1 AS (
    SELECT Std_rid,
        Act_Desc
    FROM [Table 1]
    UNPIVOT (Value FOR Act_Desc IN ([One_for_all],[Am_Water],[B2Future])) u
    WHERE u.Value = 'X'
)
SELECT ROW_NUMBER() OVER(ORDER BY t1.Std_rid) AS ID,
    t1.Std_rid,
    t2.Act_rid
FROM Unpivot_Table_1 t1
JOIN [Table 2] t2
    ON t2.Act_Desc = t1.Act_Desc;

I hope this is something for data integration. Otherwise, y'all need to shoot whomever designed this.

Test data:

CREATE TABLE dbo.[Table 1]
(
  Std_rid INT NOT NULL PRIMARY KEY,
  One_for_all VARCHAR(1),
  Am_Water VARCHAR(1),
  B2Future VARCHAR(1)
);

INSERT INTO dbo.[Table 1] (Std_rid,One_for_all,Am_Water,B2Future)
VALUES
  (1,NULL,'X',NULL),
  (2,NULL,NULL,'X'),
  (3,'X',NULL,NULL);

CREATE TABLE dbo.[Table 2]
(
    Act_rid INT NOT NULL PRIMARY KEY,
    Act_Desc VARCHAR(30)
);

INSERT INTO dbo.[Table 2] (Act_rid,Act_Desc)
VALUES
    (3,'Am_Water'),
    (6,'B2Future'),
    (1,'One_for_all');

Upvotes: 1

Related Questions