转换我行插入列数据列到行中的SQL Server
问题描述:
我的数据库表转换我行插入列数据列到行中的SQL Server
StudId Day1 Day2 Day3 Day4 Day5
-------------------------------
1 P P P P P
2 A P P P P
3 P A P A P
4 A P P P P
我多么希望我的数据是每个studentId
1
Day1 P
Day2 P
Day3 P
Day4 P
Day5 P
我怎样才能做到这一点情景?请帮助....
答
使用'union all'为每列:
假设结果列将被称为'col1',而不是'1'
Select Day1 as 'col1'
From table
Where StudId = 1
Union all
select Day2 as 'col1'
From table
Where StudId = 1
--and so on
答
尝试,
declare @tb table (StudId int, Day1 char, Day2 char, Day3 char, Day4 char, Day5 char)
insert into @tb (StudId, Day1, Day2, Day3, Day4, Day5) values
(1,'P','P','P','P','P'),
(2,'A','P','P','P','P'),
(3,'P','A','P','A','P'),
(4,'A','P','P','P','P')
select cat,[1],[2],[3],[4] from(
select t.* from @tb cross apply(values (StudId,Day1,'day1'),(StudId,Day2,'day2'),(StudId,Day3,'day3'),(StudId,Day4,'day4'),(StudId,Day5,'day5')) t (StudId,value,cat)) t
pivot
(min(value) for studid in ([1],[2],[3],[4])
)pvt