Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The PIVOT Puzzle – VI ( Is Data Present for Id – Y/ Blank )

In this puzzle we have to find for which data which Id is present, If present we have to show Y else leave that as blank. Please check the sample input and expected output.

Sample Input

TABLE_A

Id Data
P-4404 F350.0
P-0600 F350.0
P-0603 F350.0
P-6171 F350.0
P-6533 F350.0

TABLE_B

Id Data
P-4404 F353.0
P-4404 F353.1
P-0600 F353.1
P-0600 F353.0
P-5283 F353.1
P-0603 F353.0

TABLE_C

Id Data
P-4404 F555.5
P-9022 F555.5
P-2613 F555.5

Expected Output

Id F350_0 F353_0 F353_1 F555_5
P-0600 Y Y Y  
P-0603 Y Y    
P-2613       Y
P-4404 Y Y Y Y
P-5283     Y  
P-6171 Y      
P-6533 Y      
P-9022       Y

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE TABLE_A
(
  Id   VARCHAR(9),
  Data  VARCHAR(10)
)
GO

CREATE TABLE TABLE_B
(
  Id   VARCHAR(9),
  Data  VARCHAR(10)
)
GO

CREATE TABLE TABLE_C
(
  Id   VARCHAR(9),
  Data  VARCHAR(10)
)
GO

---DATA

Insert into TABLE_A (Id, Data)
Values ('P-4404', 'F350.0');
Insert into TABLE_A  (Id, Data)
Values ('P-0600', 'F350.0');
Insert into TABLE_A (Id, Data)
Values ('P-0603', 'F350.0');
Insert into TABLE_A  (Id, Data)
Values ('P-6171', 'F350.0');
Insert into TABLE_A  (Id, Data)
Values ('P-6533', 'F350.0');

----TABLE B
Insert into TABLE_B
   (Id, Data)
Values
   ('P-4404', 'F353.0');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-4404', 'F353.1');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-0600', 'F353.1');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-0600', 'F353.0');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-5283', 'F353.1');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-0603', 'F353.0');

---TABLE C

Insert into TABLE_C
   (Id, Data)
Values
   ('P-4404', 'F555.5');
Insert into TABLE_C
   (Id, Data)
Values
   ('P-9022', 'F555.5');
Insert into TABLE_C
   (Id, Data)
Values
   ('P-2613', 'F555.5');


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


SELECT  
	Id
    ,ISNULL(MAX(CASE WHEN Data = 'F350.0' THEN 'Y' END),'') [F350_0]
    ,ISNULL(MAX(CASE WHEN Data = 'F353.0' THEN 'Y' END),'') [F353_0]
    ,ISNULL(MAX(CASE WHEN Data = 'F353.1' THEN 'Y' END),'') [F353_1]
    ,ISNULL(MAX(CASE WHEN Data = 'F555.5' THEN 'Y' END),'') [F555_5]
FROM 
(
    select a.Id,a.Data FROM TABLE_A a 
    UNION ALL
    SELECT b.Id,b.Data FROM TABLE_B b
    UNION ALL
    SELECT c.Id,c.Data FROM TABLE_C c
)a
GROUP BY Id

--

Output

--

/*------------------------
SELECT  
	Id
    ,ISNULL(MAX(CASE WHEN Data = 'F350.0' THEN 'Y' END),'') [F350_0]
    ,ISNULL(MAX(CASE WHEN Data = 'F353.0' THEN 'Y' END),'') [F353_0]
    ,ISNULL(MAX(CASE WHEN Data = 'F353.1' THEN 'Y' END),'') [F353_1]
    ,ISNULL(MAX(CASE WHEN Data = 'F555.5' THEN 'Y' END),'') [F555_5]
FROM 
(
    select a.Id,a.Data FROM TABLE_A a 
    UNION ALL
    SELECT b.Id,b.Data FROM TABLE_B b
    UNION ALL
    SELECT c.Id,c.Data FROM TABLE_C c
)a
GROUP BY Id
------------------------*/
Id        F350_0 F353_0 F353_1 F555_5
--------- ------ ------ ------ ------
P-0600    Y      Y      Y      
P-0603    Y      Y             
P-2613                         Y
P-4404    Y      Y      Y      Y
P-5283                  Y      
P-6171    Y                    
P-6533    Y                    
P-9022                         Y

(8 row(s) affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com