How to get a main column and multiple sub columns in SQL

Madhukar Krishna

7y4k

I have a table in SQL Server which looks like this:

I want it to look like this:

Madhukar			Divya
6787687645	\| Mithapur, Patna	\| AN College	\|8796543456	\|Thane, Mumbai	\|Pune College

Is it possible in SQL? Any help would be appreciated. I have tried using pivot table but can't get the code right

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Madhukar Krishna7y ago
No problem guys, I found the solution:-
Select t.Madhukar,k.Madhukar,j.Madhukar,t.Divya,k.Divya,j.Divya,t.Saumya,k.Saumya,j.Saumya,t.Pushkar,k.Pushkar,j.Pushkar,t.Rishikar,k.Rishikar,j.Pushkar from (select SNAME,SMobileNo,ROW_NUMBER() over(partition by stid order by stid) rowno from Tbl_Student)s
pivot(
MIN(smobileno) for SNAME IN (Madhukar,
Divya,
Saumya,
Pushkar,
Rishikar)) as t inner Join (select SNAME,SAddress,ROW_NUMBER() over(partition by stid order by stid) rowno from Tbl_Student)s
pivot(
MIN(SAddress) for SNAME IN (Madhukar,
Divya,
Saumya,
Pushkar,
Rishikar))k
on t.rowno= k.rowno
inner Join (select SNAME,SCollegeName,ROW_NUMBER() over(partition by stid order by stid) rowno from Tbl_Student)s
pivot(
MIN(SCollegeName) for SNAME IN (Madhukar,
Divya,
Saumya,
Pushkar,
Rishikar))j
on j.rowno=t.rowno

0
Sanwar Ranwa7y ago
Can you explain what output you want ,result table is not clear ,please explain
0