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

03 Sunday Dec 2017

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

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

SQL Puzzle | The Header and Detailed Data Puzzle

03 Sunday Dec 2017

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

SQL Puzzle | The Header and Detailed Data Puzzle

In this puzzle we have to show the header data and the detailed data from shipment in header and detail fashion. Please check the sample input and expected output.