Click here to Skip to main content
15,891,828 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello All,
I have a table Test2 which have following value

C#
REG_NO         REF_NO  SUB     MARKS
16015090       015047	GSC	9
16015090       015047	SSC	28
16015090       015047	MTH	21
16015090       015047	TLS	4
16015090       015047	FLO	MP
16015090       015047	SLE	2
16033545       033114	MTH	13
16033545       033114	TLH	39
16033545       033114	FLO	12
16033545       033114	SLE	13
16033545       033114	GSC	5
16033545       033114	SSC	11
16056438       056007	FLO	19
16056438       056007	SLE	10
16056438       056007	MTH	25
16056438       056007	TLS	21
16056438       056007	GSC	23
16056438       056007	SSC	17


now i want output like this.

C#
REG_NO       REF_NO  SUB1 MARKS1  SUB2 MARKS2  SUB3 MARKS3  SUB4 MARKS4  SUB5 MARKS5  SUB6 MARKS6
16015EK01090 015047  GSC    9     SSC   28      MTH   21     TLS   4      FLO   MP      SLE   2


What I have tried:

I tried it for 2 days but still not get exact query . Please help me
Posted
Updated 10-Apr-16 21:28pm
v2

I belive that this was the solution that you need:

SQL - How to convert multiple rows into single rows with more columns? - Microsoft SQL Server - Spiceworks

Let me ask you something... I'm so noob programming, and I can't imagine what situation need to do something that you are asking. Maybe i get the same situation than you in a near future, this is the reason that i'm interested on know it.
 
Share this answer
 
I created a table with your sample data

C#
create table StudentMark (REG_NO int,REF_NO int,SUB varchar(3),MARKS int)

insert into StudentMark values 
(16015090,015047,       'GSC',  9),
(16015090,015047,	'SSC',	28),
(16015090,015047,	'MTH',	21),
(16015090,015047,	'TLS',	4),
(16015090,015047,	'FLO',	44),
(16015090,015047,	'SLE',	2),
(16033545,033114,	'MTH',	13),
(16033545,033114,	'TLH',	39),
(16033545,033114,	'FLO',	12),
(16033545,033114,	'SLE',	13),
(16033545,033114,	'GSC',	5),
(16033545,033114,	'SSC',	11),
(16056438,056007,	'FLO',	19),
(16056438,056007,	'SLE',	10),
(16056438,056007,	'MTH',	25),
(16056438,056007,	'TLS',	21),
(16056438,056007,	'GSC',	23),
(16056438,056007,	'SSC',	17)

select * from StudentMark


Solution Query
SQL
select ROW_NUMBER()over(partition by REG_NO order by SUB)Slno,* into #prps from StudentMark

select distinct REG_NO,REF_NO into #detail from StudentMark

DECLARE @slno int
select @slno =max(slno) from #prps
DECLARE @i varchar(10),@alter varchar(4000),@value varchar(4000)
SET @i=1
while(@i<=@slno)
begin
select @alter='alter table #detail add SUB'+@i+' varchar(100),Marks'+@i+' varchar(100)'
select @value='update #detail set SUB'+@i+'=b.sub,Marks'+@i+'=b.Marks from #detail a join #prps b on a.REG_NO=b.REG_NO where slno='+@i
exec (@alter)
exec (@value)
set @i=@i+1
end

select * from #detail


Output

C#
REG_NO	REF_NO	SUB1	Marks1	SUB2	Marks2	SUB3	Marks3	SUB4	Marks4	SUB5	Marks5	SUB6	Marks6
16015090	15047	FLO	44	GSC	9	MTH	21	SLE	2	SSC	28	TLS	4
16033545	33114	FLO	12	GSC	5	MTH	13	SLE	13	SSC	11	TLH	39
16056438	56007	FLO	19	GSC	23	MTH	25	SLE	10	SSC	17	TLS	21
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900