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.

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