如何将多行检索到单行 [英] How to retrieve multiple rows into single row
问题描述
Hello All,
我有一张表Test2,它有以下价值
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
现在
我想要这样的输出。
REG_NO REF_NO SUB1 MARKS1 SUB2 MARKS2 SUB3 MARKS3 SUB4 MARKS4 SUB5 MARKS5 SUB6 MARKS6
16015EK01090 < span class =code-digit> 015047 GSC 9 SSC 28 MTH 21 TLS 4 FLO MP SLE 2
我的尝试:
我试了两天但仍未得到确切的查询。请帮帮我
我相信这是你需要的解决方案:
SQL - 如何将多行转换为单行有更多列的行? - Microsoft SQL Server - Spiceworks
让我问你一些事情......我是如此编程,我无法想象需要什么情况做你要问的事。也许我在不久的将来会遇到与你相同的情况,这就是我有兴趣了解它的原因。
我用你的样本数据创建了一个表格
创建表StudentMark(REG_NO int ,REF_NO int ,SUB varchar( 3 ),MARKS int )
将插入 StudentMark值
( 16015090 , 015047 ,' GSC', 9 ),
( 16015090 , 015047 ,' SSC', 28 ),
( 16015090 , 015047 ,' MTH', 21 ),
( 16015090 , 015047 ,' < span class =code-string> 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 )
选择 * 来自 StudentMark
解决方案查询
< pre lang =SQL> 选择 ROW_NUMBER() over (分区 按 REG_NO 订单 by SUB)Slno,* into #prps from StudentMark
选择 distinct REG_NO,REF_NO into #detail 来自 StudentMark
DECLARE @ slno int
选择 @slno = max(slno)来自 #prps
DECLARE @ i varchar ( 10 ), @ alter varchar ( 4000 ), @ value varchar ( 4000 )
SET @ i = 1
while (@ i< = @ slno)
开始
选择 @ alter = ' alter table #detail add SUB' + @ i + ' varchar(100),Marks' + @ i + ' varchar(100)'
选择 @ value = ' update #detail set SUB' + @ i + ' = b.sub,Marks' + @ i + ' = b.Marks来自#detail a join #prps b on a.REG_NO = b.REG_NO where slno =' + @ i
< span class =code-keyword> exec ( @ alter )
exec ( @ value )
set @ i = @ i + 1
< span class =code-keyword> end
选择 * 来自 #detail
输出
REG_NO REF_NO SUB1标记1 SUB2标记2 SUB3标记3 SUB4标记4 SUB5标记5 SUB6标记6
16015090 15047 FLO 4 4 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
Hello All,
I have a table Test2 which have following value
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.
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
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.
I created a table with your sample data
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
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
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
这篇关于如何将多行检索到单行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!