SQL pivot table query and hookup query in hierarchical format

Question

Table-1

Resource    Rank1   Rank2   Rank3   Rank4
Critical    3         3         6    12
High        8         8        12     24

Table-2

Name     Issue      SLC   
RAM     Critical    Rank1   
KIRAN   Critical    Rank2
BASS    High        Rank3
LOUT    Critical    Rank4
chinna  High        Rank1
lavanya Critical    Rank3
babau   High        Rank4
rani    Critical    Rank2

Expected out should

Name    Issue       SLC   values RESOURCEMISS
RAM     Critical    Rank1   3      Resource 
KIRAN   Critical    Rank2   3      Resource 
BASS    High        Rank3  12      Resource 
LOUT    Critical    Rank4  12       Resource    
chinna  High        Rank1  8      Resource  
lavanya Critical    Rank3   6      Resource 
babau   High        Rank4  24    Resource   
rani    Critical    Rank2  3     Resource

This should not be like [RESOURCEMISS]='Resoure'

Srikanth When you present code and sample date please format by selecting a block and pressing ctrl-k — P.Salmon, Mar 09 '19 at 12:31
Any time you find yourself with enumerated columns (above, say, 2), you can be sure that your schema design is suboptimal — Strawberry, Mar 09 '19 at 12:33

Tim Biegeleisen · Answer 1 · 2019-03-09T12:50:28.860

A somewhat ugly solution, using a correlated subquery to lookup each rank and issue:

SELECT
    Name,
    Issue,
    SLC,
    (SELECT CASE WHEN t2.SLC = 'Rank1' THEN t1.Rank1
                 WHEN t2.SLC = 'Rank2' THEN t1.Rank2
                 WHEN t2.SLC = 'Rank3' THEN t1.Rank3
                 WHEN t2.SLC = 'Rank4' THEN t1.Rank4 END
     FROM table1 t1
     WHERE t1.Resource = t2.Issue) "values"
FROM table2 t2;

Demo

I don't know what database you are actually using (MySQL or SQL Server), but it might not even matter much, given your current design. Your schema is not normalized, and ideally the rank number should be an actual value in a column somewhere, rather than the column name itself. It seems that @strawberry has suggested one refactor of your design which makes it much easier to proceed, using just a single inner join.

Thanks a lot for give a right answer to my question,it is working sqlserver. you guys saved my time .Thanks for your good work — Srikanth, Mar 09 '19 at 14:50

score 0 · Accepted Answer · answered Mar 09 '19 at 12:47

Consider a solution along the following lines (anything markedly different from this is frankly wasting your time):

DROP TABLE IF EXISTS table1;

CREATE TABLE table1
(resource VARCHAR(12) NOT NULL
,slc_rank INT NOT NULL
,val INT NOT NULL
,PRIMARY KEY(resource,slc_rank)
);

INSERT INTO table1 VALUES
('Critical',1,3),
('Critical',2,3),
('Critical',3,6),
('Critical',4,12),
('High',1, 8),
('High',2, 8),
('High',3,12),
('High',4,24);

DROP TABLE IF EXISTS table2;

CREATE TABLE table2
(name VARCHAR(20) NOT NULL
,resource VARCHAR(12) NOT NULL
,slc_rank INT NOT NULL
,PRIMARY KEY(name,resource,slc_rank)
);

INSERT INTO table2 VALUES
('RAM','Critical',1),
('KIRAN','Critical',2),
('BASS','High',3),
('LOUT','Critical',4),
('chinna','High',1),
('lavanya','Critical',3),
('babau','High',4),
('rani','Critical',2);

SELECT x.*
     , y.val 
  FROM table2 x 
  JOIN table1 y 
    ON y.resource = x.resource 
   AND y.slc_rank = x.slc_rank;
+---------+----------+----------+-----+
| name    | resource | slc_rank | val |
+---------+----------+----------+-----+
| babau   | High     |        4 |  24 |
| BASS    | High     |        3 |  12 |
| chinna  | High     |        1 |   8 |
| KIRAN   | Critical |        2 |   3 |
| lavanya | Critical |        3 |   6 |
| LOUT    | Critical |        4 |  12 |
| RAM     | Critical |        1 |   3 |
| rani    | Critical |        2 |   3 |
+---------+----------+----------+-----+

But the bigger question is whether Frank actually goes around wasting other peoples' time :P — Tim Biegeleisen, Mar 09 '19 at 12:51
@TimBiegeleisen The answer's 'no', because mostly he just wastes mine :-( — Strawberry, Mar 09 '19 at 12:55

score -1 · Answer 3 · answered Mar 09 '19 at 12:40

Think you could try something like this:

SELECT 
    Name
    Issue
    SLC
    Values.value
FROM
    Table-2
LEFT JOIN
    (
        SELECT 'Rank1' descrip, Rank1 value FROM Table-1
        UNION ALL
        SELECT 'Rank2' descrip, Rank1 value FROM Table-1
        UNION ALL
        SELECT 'Rank3' descrip, Rank1 value FROM Table-1
        UNION ALL
        SELECT 'Rank4' descrip, Rank1 value FROM Table-1
    ) as Values
ON Values.descrip = Issue

based on Mysql Convert Column to row (Pivot table )

SQL pivot table query and hookup query in hierarchical format

3 Answers3

Demo