8 replies

Last post Oct 26, 2016 07:33 AM by Zhi Lv - MSFT

• Asp.net king

Member

15 Points

146 Posts

Oct 25, 2016 04:58 PM|Asp.net king|LINK

I have two tables (Students and Skills) , i want query to make the following table :

 Student Number Student Name Skill 1 Skill 2 Skill n 423245 X 90 83 70 234235 Y 7 78 65
“First, solve the problem. Then, write the code.”
(John Johnson)
• codemovement...

Contributor

2290 Points

794 Posts

Hi King,

I presume student and skills table have relation with each other where Student Number is primary key in Student table and used as foreign key in Skills table

```select p.Student Number, p.Student Name, a.Skill 1, a.Skill 2 , a.Skill n
from Students p
inner join Skills a on a.Student Number= p.Student Number
```

Hope it Helps

If my solution works for you, please mark it as answer! It will help others.

Thanks,
• Rion William...

All-Star

113010 Points

18361 Posts

MVP

Oct 25, 2016 06:49 PM|Rion Williams|LINK

What do the definitions of both of your tables look like? If you don't explicitly have a column actually called Skill 1, Skill 2, etc. then you'll likely need to use a PIVOT within your query (which can apply specific rows as columns).

• Asp.net king

Member

15 Points

146 Posts

Oct 25, 2016 08:24 PM|Asp.net king|LINK

Yes i need a pivot table, but how i can do this?

“First, solve the problem. Then, write the code.”
(John Johnson)
• senthilwaits

Contributor

3290 Points

866 Posts

Regards,

Senthil Kumar Sundaram
• Asp.net king

Member

15 Points

146 Posts

Oct 25, 2016 08:33 PM|Asp.net king|LINK

Join don't solve the problem, i want to convert columns to rows. Any suggestions?

“First, solve the problem. Then, write the code.”
(John Johnson)
• codemovement...

Contributor

2290 Points

794 Posts

http://stackoverflow.com/questions/17073134/sql-server-join-tables-and-pivot

If my solution works for you, please mark it as answer! It will help others.

Thanks,
• Asp.net king

Member

15 Points

146 Posts

Oct 25, 2016 08:39 PM|Asp.net king|LINK

the problem when i use pivot , that i must use aggregate function ? i don't need any aggregate function !

“First, solve the problem. Then, write the code.”
(John Johnson)
• Zhi Lv - MSF...

All-Star

44701 Points

6713 Posts

Microsoft

Oct 26, 2016 07:33 AM|Zhi Lv - MSFT|LINK

Hi John,

asp.net King

the problem when i use pivot , that i must use aggregate function ? i don't need any aggregate function !

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

More details, see: https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

So, we need to use aggregate function when using pivot. You could try to use the Max function.

Best regards,
Dillion

MSDN Community Support