带有文本行的主SQL表。

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

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

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