CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mario Soft
Mario Soft

Reputation: 61

Select Data From Two Tables SQL

I have one Table (SeekerInfo) that like :

--------------------------------------------------
SeekerID -  SeekerName - SeekerSex - SeekerMobile
12121    -  mmmm       - Male      - 067676767
13223    -  ssss       - Female    - 078876767
--------------------------------------------------

and another table (SeekerCources) that like (every seeker maximum have 3 cources): 

--------------------------------------------------
SeekerID - CourceName - Duration
12121    - MCSA       - 1 year
12121    - MCPD       - 6 months
13223    - CCNA       - 1 year
13223    - CCNP       - 1 year
13223    - MCTS       - 4 months

I want to make a select statement that preview data from two Tables look like this:

SeekerID -  SeekerName - SeekerSex - SeekerMobile - Cource1 - Cource2 - Cource3
12121    -  mmmm       - Male      - 067676767    - MCSA    - MCPD    - *NULL*
13223    -  ssss       - Female    - 078876767    - CCNA    - CCNP    - MCTS

Upvotes: 1

Views: 1257

Answers (3)

DxTx
DxTx

Reputation: 3357

In here, I have tried to show your answer in three step.

CREATE TABLE [SeekerCources](
    [SeekerID] [nvarchar](50) NULL,
    [CourceName] [nvarchar](50) NULL,
    [Duration] [nvarchar](50) NULL
) 


CREATE TABLE [SeekerInfo](
    [SeekerID] [nvarchar](50) NULL,
    [SeekerName] [nvarchar](50) NULL,
    [SeekerSex] [nvarchar](50) NULL,
    [SeekerMobile] [nvarchar](50) NULL
)

INSERT [SeekerCources] ([SeekerID], [CourceName], [Duration]) VALUES (N'12121', N'MCSA', N'1 year')
INSERT [SeekerCources] ([SeekerID], [CourceName], [Duration]) VALUES (N'12121', N'MCPD', N'6 months')
INSERT [SeekerCources] ([SeekerID], [CourceName], [Duration]) VALUES (N'13223', N'CCNA', N'1 year')
INSERT [SeekerCources] ([SeekerID], [CourceName], [Duration]) VALUES (N'13223', N'CCNP', N'1 year')
INSERT [SeekerCources] ([SeekerID], [CourceName], [Duration]) VALUES (N'13223', N'MCTS', N'4 months')
INSERT [SeekerInfo] ([SeekerID], [SeekerName], [SeekerSex], [SeekerMobile]) VALUES (N'12121', N'mmmm', N'Male', N'067676767')
INSERT [SeekerInfo] ([SeekerID], [SeekerName], [SeekerSex], [SeekerMobile]) VALUES (N'13223', N'ssss', N'Female', N'078876767')

Step 1

Select SeekerID, CourceName, 
'Course' + CAST(ROW_NUMBER() over (partition by SeekerID order by CourceName) as varchar(10)) as CourseSquence
from SeekerCources

The step 1 result should be as follows.

Step 1 Result

Demo: link

Step 2 

Select SeekerID, Course1, Course2, Course3
from    
(
    select SeekerID, CourceName, 
    'Course' + CAST(ROW_NUMBER() over (partition by SeekerID order by CourceName) as varchar(10)) as CourseSquence
    from SeekerCources  
) Temp
Pivot
(
    Max(CourceName)
    For CourseSquence in (Course1, Course2, Course3)
) PIV

The step 2 result should be as follows.

Step 2 Result

Demo: link

Step 3

Select SeekerInfo.*, PIV.SeekerID, Course1, Course2, Course3
from    
(
    select SeekerID, CourceName, 
    'Course' + CAST(ROW_NUMBER() over (partition by SeekerID order by CourceName) as varchar(10)) as CourseSquence
    from SeekerCources  
) Temp
Pivot
(
    Max(CourceName)
    For CourseSquence in (Course1, Course2, Course3)
) PIV
inner join SeekerInfo on SeekerInfo.SeekerID = PIV.SeekerID

The step 3 result should be as follows.

Step 3 Result

Demo: link

Explanation
Video 1: Pivot in SQL Server - https://youtu.be/h3BtudZehuo
Video 2: Transform rows into columns - https://youtu.be/C0mQqDnF7wQ

Upvotes: 0

simonstuck
simonstuck

Reputation: 58

It looks like you are going to have a lot of NULL values in the long term which means that you will certainly run into problems sooner or later. Also, you are trying to introduce n number of attributes, n being the largest number of course results, in your result which doesn't make things easier on the client side.

May I suggest a different approach where you will get all results as rows, which means that you will have duplicate data in your result (This doesn't really matter though as you're not storing that).

SELECT i.*, c.CourseName 
FROM SeekerInfo i JOIN SeekerCourses c 
ON i.SeekerID = c.SeekerID;

This will return all courses with the additional seeker information ;)

EDIT: Sorry, I didn't properly read your post - you will always have a maximum of 3 courses. Still, maybe this helps anyway.

Upvotes: 1

Conrad Frix
Conrad Frix

Reputation: 52655

Here's an approach where you assign the number 1-3 to each of the courses and then do three joins 

WITH cte 
     AS (SELECT Row_number() OVER (partition BY SeekerID ORDER BY CourceName) rn 
                , 
                SeekerID, 
                CourceName, 
                Duration 
         FROM   SeekerCources) 
SELECT si.SeekerID, 
       si.SeekerName, 
       si.SeekerSex, 
       si.SeekerMobile, 
       c1.CourceName AS Cource1, 
       c2.CourceName AS Cource2, 
       c3.CourceName AS Cource3 
FROM   SeekerInfo si 
       LEFT JOIN cte c1 
         ON si.SeekerID = c1.SeekerID 
            AND c1.rn = 1 
       LEFT JOIN cte c2 
         ON si.SeekerID = c2.SeekerID 
            AND c2.rn = 2 
       LEFT JOIN cte c3 
         ON si.SeekerID = c3.SeekerID 
            AND c3.rn = 3

DEMO

One unresolved problem is that we don't have a good way to decide which course goes in 1,2,or 3 perhaps if you had a field like date taken you could replace CourceName with that

e.g.
Row_number() OVER (partition BY SeekerID ORDER BY DateTaken)

Upvotes: 1

Related Questions