Tags

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


SQL Puzzle | The Ids Puzzle

This puzzle is bit different puzzle. The situation is simple, you have to create a new column and that column should have comma separated Ids based on each type.

For details please check out the sample input and the expected output below-

Sample Input

Base table Data (Ist table)

ID Name
1 TV
2 Table
3 Chair
4 Fan
5 Sofa
6 Oven

BaseDetails table Data (2nd table)

ID Type 1 Type 2 Type 3 Type 4 Type 5 Type 6 Type 7 Type 8 Type 9
1 Table Fan TV Sofa NULL TV Sofa Oven TV
2 TV Table Chair Table Oven NULL NULL Sofa Sofa
3 Oven Sofa NULL NULL NULL NULL NULL NULL NULL

Expected Output

ID Type 1 Type 2 Type 3 Type 4 Type 5 Type 6 Type 7 Type 8 Type 9 TypeIds
1 Table Fan TV Sofa NULL TV Sofa Oven TV 2,4,1,5,1,5,6,1
2 TV Table Chair Table Oven NULL NULL Sofa Sofa 1,2,3,2,6,5,5
3 Oven Sofa NULL NULL NULL NULL NULL NULL NULL 6,5

Rules/Restrictions

  • The solution should be should use “SELECT” statement or a CTE.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--


CREATE TABLE Base 
(
	 ID TINYINT
	,Name VARCHAR(30)
)
GO


INSERT INTO Base VALUES
(1, 'TV'),
(2, 'Table'),
(3, 'Chair'),
(4, 'Fan'),
(5, 'Sofa'),
(6, 'Oven')
GO

CREATE TABLE BaseDetails
(
	ID int,
	[Type 1] VARCHAR(30),
	[Type 2] VARCHAR(30),
	[Type 3] VARCHAR(30),
	[Type 4] VARCHAR(30),
	[Type 5] VARCHAR(30),
	[Type 6] VARCHAR(30),
	[Type 7] VARCHAR(30),
	[Type 8] VARCHAR(30),
	[Type 9] VARCHAR(30)
)
GO

INSERT INTO BaseDetails VALUES 
(1,'Table','Fan','TV','Sofa',NULL,'TV','Sofa','Oven','TV'),
(2,'TV','Table','Chair','Table','Oven',NULL,NULL,'Sofa','Sofa'),
(3, 'Oven','Sofa',NULL,NULL,NULL,NULL,NULL,NULL,NULL)
GO


--

Solution 1


--

select *  
,replace(rtrim(ltrim(isnull((select cast(id as varchar) from Base I where I.Name =c.[Type 1]+' '),'') + 
  ISNULL((select ' ' + cast(id as varchar) from Base I where I.Name =c.[Type 2]+' '),'') + 
  ISNULL((select ' ' + cast(id as varchar) from Base I where I.Name =c.[Type 3]+' '),'') + 
  ISNULL((select ' ' + cast(id as varchar) from Base I where I.Name =c.[Type 4]+' '),'') + 
  ISNULL((select ' ' + cast(id as varchar) from Base I where I.Name =c.[Type 5]+' '),'') + 
  ISNULL((select ' ' + cast(id as varchar) from Base I where I.Name =c.[Type 6]+' '),'') + 
  ISNULL((select ' ' + cast(id as varchar) from Base I where I.Name =c.[Type 7]+' '),'') +  
  ISNULL((select ' ' + cast(id as varchar) from Base I where I.Name =c.[Type 8]+' '),'') +  
  ISNULL((select ' ' + cast(id as varchar) from Base I where I.Name =c.[Type 9]+' '),''))),' ',',') as TypeIds
from BaseDetails c

--

Solution 2


--

select c.* ,replace( rtrim(ltrim(isnull( cast ( i.id as varchar),'')+isnull(' '+ cast (i1.id as varchar),'') + isnull(' '+cast (i2.id as varchar),'') 
+isnull(' '+ cast (i3.id as varchar),'')+
isnull(' '+ cast (i4.id as varchar),'') + isnull(' '+ cast (i5.id as varchar),'')
+isnull(' '+ cast (i6.id as varchar),'')+isnull(' '+ cast (i7.id as varchar),'') +isnull(' '+ cast (i8.id as varchar),''))),' ',',')from   BaseDetails c
left join  Base i on i.Name=c.[Type 1] 
left join Base i1 on i1.name=c.[Type 2]
left join Base i2 on i2.name=c.[Type 3]
left join Base i3 on i3.name=c.[Type 4]
left  join Base i4 on i4.name=c.[Type 5]
left join Base i5 on i5.name=c.[Type 6]
left join Base i6 on i6.name=c.[Type 7]
left join Base i7 on i7.name=c.[Type 8]
left join Base i8 on i8.name=c.[Type 9]

--

Solution 3


--

SELECT ID,[Type 1] ,[Type 2] ,[Type 3] ,[Type 4] ,[Type 5] ,[Type 6] 
,[Type 7] ,[Type 8] ,[Type 9] ,
               STUFF(
                    (SELECT ','+CONVERT(VARCHAR(30),TypeID)
                    FROM         (
                                SELECT bd.ID,b.ID AS TypeID
                                FROM Base b
                                      INNER JOIN BaseDetails bd ON bd.[Type 1] = b.Name
                                UNION ALL
                                SELECT bd.ID,b.ID AS TypeID
                                FROM Base b
                                      INNER JOIN BaseDetails bd ON bd.[Type 2] = b.Name
                                UNION ALL
                                SELECT bd.ID,b.ID AS TypeID
                                FROM Base b
                                      INNER JOIN BaseDetails bd ON bd.[Type 3] = b.Name                    
                                UNION ALL
                                SELECT bd.ID,b.ID AS TypeID
                                FROM Base b
                                      INNER JOIN BaseDetails bd ON bd.[Type 4] = b.Name                    
                                UNION ALL
                                SELECT bd.ID,b.ID AS TypeID
                                FROM Base b
                                      INNER JOIN BaseDetails bd ON bd.[Type 5] = b.Name                    
                                UNION ALL
                                SELECT bd.ID,b.ID AS TypeID
                                FROM Base b
                                      INNER JOIN BaseDetails bd ON bd.[Type 6] = b.Name                    
                                UNION ALL
                                SELECT bd.ID,b.ID AS TypeID
                                FROM Base b
                                      INNER JOIN BaseDetails bd ON bd.[Type 7] = b.Name                    
                                UNION ALL
                                SELECT bd.ID,b.ID AS TypeID
                                FROM Base b
                                      INNER JOIN BaseDetails bd ON bd.[Type 8] = b.Name                    
                                UNION ALL
                                SELECT bd.ID,b.ID AS TypeID
                                FROM Base b
                                      INNER JOIN BaseDetails bd ON bd.[Type 9] = b.Name
                             ) u
                             WHERE u.ID = x.ID
                             FOR XML PATH('')),1,1,'') AS TypeIDs               
FROM BaseDetails x

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements