SQL Puzzle | The Ids Puzzle (Solutions :)) | Improving my SQL BI Skills

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

3 thoughts on “SQL Puzzle | The Ids Puzzle (Solutions :))”

shivam (@shivam0408) said:

July 20, 2016 at 11:09 PM

select [Type 1] ,
[Type 2] ,
[Type 3] ,
[Type 4] ,
[Type 5] ,
[Type 6] ,
[Type 7] ,
[Type 8] ,
[Type 9] ,b.ID
into #temp
from BaseDetails a , Base b where a.[Type 1]=b.Name or
a.[Type 2]=b.Name or a.[Type 3]=b.Name or a.[Type 3]=b.Name or a.[Type 4]=b.Name or
a.[Type 5]=b.Name or a.[Type 6]=b.Name or a.[Type 7]=b.Name or a.[Type 8]=b.Name or
a.[Type 9]=b.Name

select distinct [Type 1] ,
[Type 2] ,
[Type 3] ,
[Type 4] ,
[Type 5] ,
[Type 6] ,
[Type 7] ,
[Type 8] ,
[Type 9],STUFF((SELECT ‘,’ + cast (ID as nvarchar(max))
FROM #temp t1 where t1.[Type 1]=t.[Type 1]
ORDER BY ID
FOR XML PATH(”)), 1, 1, ”) from #temp t

LikeLike

Pawan Kumar Khowal said:

July 21, 2016 at 8:25 AM

Here we are getting distinct Ids but in this case we need all Ids even if they are repeating. Check out the expected output.

LikeLike

Balaji Ganesan (@BalajiGanesan1) said:

August 31, 2016 at 3:02 PM

;WITH CTE AS (

SELECT* FROM (
SELECT ID as CT_ID,TYPEID,Val FROM(
SELECT ID,[Type 1],[Type 2],[Type 3],[Type 4],[Type 5],[Type 6],[Type 7],[Type 8],[Type 9]
from BaseDetails)X
UNPIVOT
(Val FOR TYPEID IN
([Type 1],[Type 2],[Type 3],[Type 4],[Type 5],[Type 6],[Type 7],[Type 8],[Type 9])
)
AS UNPVT
)A INNER JOIN BASE B ON B.Name = A.val
)

SELECT DISTINCT B.*,
STUFF((SELECT ‘,’+CAST(X.ID as Nvarchar(10)) FROM CTE as X WHERE X.CT_ID= A.ID FOR XML PATH(”) ),1,1,”)as TypeID
FROM BASE A INNER JOIN BaseDetails B ON a.Id = B.id

LikeLiked by 1 person

M	T	W	T	F	S	S
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

Share this

Related

3 thoughts on “SQL Puzzle | The Ids Puzzle (Solutions :))”

Leave a comment Cancel reply