I am trying to pivot a microsoft SQL database table but i can not get it to work. The examples that i can find are with numbers and add or join them.
我正在尝试使用microsoft SQL数据库表,但是我无法让它正常工作。我能找到的例子是关于数字的,添加或加入它们。
This is what i got so far, but with little succes:
这是我到目前为止得到的,但很少成功:
select *
from
(
select CODE, LangID , Text
from dbo.REJECT_CODES
) src
pivot
(
Text
for LangID in ([1], [2], [3], [4])
) piv;
This is an example of a table that i want to pivot:
这是我想要转到的一个表格的例子:
Code LangID Text
--------------------------------------------
1 1 Text1
1 2 Text2
1 3 Text3
1 4 Text4
2 1 Text5
2 2 Text6
2 3 Text7
2 4 Text8
And this is the result that i want to get:
这就是我想要的结果
Code TextA TextB TextC TextD
----------------------------------------------
1 Text1 Text2 Text3 Text4
2 Text5 Text6 Text7 Text8
2 个解决方案
#1
3
try this :
试试这个:
create table #temp (code int, languageID int, text varchar(10))
insert into #temp values
(1,1,'Text1'),
(1,2,'Text2'),
(1,3,'Text3'),
(1,4,'Text4'),
(2,1,'Text5'),
(2,2,'Text6'),
(2,3,'Text7'),
(2,4,'Text8')
select CODE, [1] As TextA, [2] As TextB, [3] As TextC, [4] As TextD
from
(
select CODE, LanguageID , Text
from #temp
) src
pivot
(
max(Text)
for LanguageID in ([1], [2], [3], [4])
) piv;
op:
人事处:
CODE TextA TextB TextC TextD
----------- ---------- ---------- ---------- ----------
1 Text1 Text2 Text3 Text4
2 Text5 Text6 Text7 Text8
#2
1
Try with an aggregation function like max:
尝试使用max这样的聚合函数:
declare @tmp table( Code int, LangID int, Text nvarchar(50))
insert into @tmp select 1,1,'Text1'
insert into @tmp select 1,2,'Text2'
insert into @tmp select 1,3,'Text3'
insert into @tmp select 1,4,'Text4'
insert into @tmp select 2,1,'Text5'
insert into @tmp select 2,2,'Text6'
insert into @tmp select 2,3,'Text7'
insert into @tmp select 2,4,'Text8'
select * from @tmp
select *
from
(
select CODE, LangID , Text
from @tmp
) src
pivot
(
max(Text)
for LangID in ([1], [2], [3], [4])
) piv;
result:
结果:
+------+-------+-------+-------+-------+
| CODE | 1 | 2 | 3 | 4 |
+------+-------+-------+-------+-------+
| 1 | Text1 | Text2 | Text3 | Text4 |
| 2 | Text5 | Text6 | Text7 | Text8 |
+------+-------+-------+-------+-------+
#1
3
try this :
试试这个:
create table #temp (code int, languageID int, text varchar(10))
insert into #temp values
(1,1,'Text1'),
(1,2,'Text2'),
(1,3,'Text3'),
(1,4,'Text4'),
(2,1,'Text5'),
(2,2,'Text6'),
(2,3,'Text7'),
(2,4,'Text8')
select CODE, [1] As TextA, [2] As TextB, [3] As TextC, [4] As TextD
from
(
select CODE, LanguageID , Text
from #temp
) src
pivot
(
max(Text)
for LanguageID in ([1], [2], [3], [4])
) piv;
op:
人事处:
CODE TextA TextB TextC TextD
----------- ---------- ---------- ---------- ----------
1 Text1 Text2 Text3 Text4
2 Text5 Text6 Text7 Text8
#2
1
Try with an aggregation function like max:
尝试使用max这样的聚合函数:
declare @tmp table( Code int, LangID int, Text nvarchar(50))
insert into @tmp select 1,1,'Text1'
insert into @tmp select 1,2,'Text2'
insert into @tmp select 1,3,'Text3'
insert into @tmp select 1,4,'Text4'
insert into @tmp select 2,1,'Text5'
insert into @tmp select 2,2,'Text6'
insert into @tmp select 2,3,'Text7'
insert into @tmp select 2,4,'Text8'
select * from @tmp
select *
from
(
select CODE, LangID , Text
from @tmp
) src
pivot
(
max(Text)
for LangID in ([1], [2], [3], [4])
) piv;
result:
结果:
+------+-------+-------+-------+-------+
| CODE | 1 | 2 | 3 | 4 |
+------+-------+-------+-------+-------+
| 1 | Text1 | Text2 | Text3 | Text4 |
| 2 | Text5 | Text6 | Text7 | Text8 |
+------+-------+-------+-------+-------+