I am trying to turn a table 90 degrees: make columns rows. No PIVOT is allowed since PIVOT requires aggregate functions.
我试图将表格旋转90度:使列行。由于PIVOT需要聚合函数,因此不允许PIVOT。
Example: I have a table with the columns:
ID int,
ISO char(2),
Text varchar(255).
示例:我有一个包含列的表:ID int,ISO char(2),Text varchar(255)。
So I have this:
所以我有这个:
ID ISO Text -- --- ---- 1 DE Auto 2 EN Car
I'd like to get the following:
我想得到以下内容:
ID EN DE -- --- ---- 1 Car Auto
How do you accomplish that?
你是如何实现这一目标的?
6 个解决方案
#1
5
This answer is really frantisek's, I'm just copying here to correct the mistake (I can't edit directly).
这个答案真的是frantisek的,我只是在这里复制以纠正错误(我不能直接编辑)。
Basically, you use that solution, with a tweak:
基本上,您使用该解决方案,并进行调整:
SELECT
max(DE) as DE, max(EN) as EN
FROM
test
PIVOT (MAX([text]) FOR ISO in (DE,EN)) p
This will get the content into a single row. Also, it drops the ID, since it doesn't make sense if you want a single row (there is no logic to indicate what to do with it when combining into a single row).
这将使内容成为一行。此外,它会丢弃ID,因为如果您想要一行(在组合成单行时没有逻辑来指示如何处理它),这是没有意义的。
Also, the assumption is made that the values in the ISO column are unique, otherwise, this will lose data due to the MAX
aggregate.
此外,假设ISO列中的值是唯一的,否则,由于MAX聚合,这将丢失数据。
#2
2
I found the solution as the following:
我发现解决方案如下:
SELECT
ID, DE, EN
FROM
TextTable
PIVOT(MAX([text]) FOR ISO IN (DE,EN)) p
It's possible to use PIVOT with MAX aggregating function over the text.
可以在文本上使用具有MAX聚合功能的PIVOT。
#3
1
Query without a PIVOT even though other answers prove you can use a PIVOT :)
查询没有PIVOT,即使其他答案证明你可以使用PIVOT :)
SELECT
MAX(DE.Text) AS DE,
MAX(EN.Text) AS EN
FROM TextTable AS TT
LEFT JOIN TextTable AS DE
ON DE.ID = TT.ID
AND DE.ISO = 'DE'
LEFT JOIN TextTable AS EN
ON EN.ID = TT.ID
AND EN.ISO = 'EN'
#4
1
If you try this solution and get a syntax error try setting the compatability mode of your database via
如果您尝试此解决方案并获得语法错误,请尝试通过设置数据库的兼容性模式
ALTER DATABASE myDatabase SET COMPATIBILITY_LEVEL = 90;
This will set the compatability to SQLServer 2005 and the above queries will execute w/o a syntax error.
这将设置SQLServer 2005的兼容性,上述查询将执行没有语法错误。
#5
1
select
t.num_claim_no,
rtrim (xmlagg (xmlelement (e, t.txt_remarks ||'@'|| t.dat_update_date || ' , ')).extract ('//text()'), ',') Remarks,
rtrim (xmlagg (xmlelement (e, t.num_update_no || ' , ')).extract ('//text()'), ',') SrlNo
from
gc_clm_gen_info t
where t.txt_remarks is not null
group by
t.num_claim_no
;
#6
0
Since you explicitly asked for a non-pivot solution: this should work, if you know which ISOs you will have in the rows. I called the table "Test".
由于您明确要求非透视解决方案:如果您知道行中将包含哪些ISO,这应该可行。我把表称为“测试”。
declare @temp table ([ID] int, [de] varchar(255), [en] varchar(255)) -- add ISOs if necessary
INSERT @temp
SELECT distinct [ID], '', '' from Test -- ADD '' for other ISOs if necessary
DECLARE c CURSOR read_only
FOR SELECT [ID], [ISO], [Text] from test
DECLARE @ID int, @ISO char(2), @Text varchar(255)
OPEN c
FETCH NEXT FROM c INTO @ID, @ISO, @Text
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
UPDATE @temp
SET [DE] = case when @ISO = 'DE' then @Text else [de] end,
[EN] = case when @ISO = 'EN' then @Text else [en] end
-- add ISOs if necessary
WHERE [ID] = @ID
END
FETCH NEXT FROM c INTO @ID, @ISO, @Text
END
CLOSE c
DEALLOCATE c
SELECT * FROM @temp
#1
5
This answer is really frantisek's, I'm just copying here to correct the mistake (I can't edit directly).
这个答案真的是frantisek的,我只是在这里复制以纠正错误(我不能直接编辑)。
Basically, you use that solution, with a tweak:
基本上,您使用该解决方案,并进行调整:
SELECT
max(DE) as DE, max(EN) as EN
FROM
test
PIVOT (MAX([text]) FOR ISO in (DE,EN)) p
This will get the content into a single row. Also, it drops the ID, since it doesn't make sense if you want a single row (there is no logic to indicate what to do with it when combining into a single row).
这将使内容成为一行。此外,它会丢弃ID,因为如果您想要一行(在组合成单行时没有逻辑来指示如何处理它),这是没有意义的。
Also, the assumption is made that the values in the ISO column are unique, otherwise, this will lose data due to the MAX
aggregate.
此外,假设ISO列中的值是唯一的,否则,由于MAX聚合,这将丢失数据。
#2
2
I found the solution as the following:
我发现解决方案如下:
SELECT
ID, DE, EN
FROM
TextTable
PIVOT(MAX([text]) FOR ISO IN (DE,EN)) p
It's possible to use PIVOT with MAX aggregating function over the text.
可以在文本上使用具有MAX聚合功能的PIVOT。
#3
1
Query without a PIVOT even though other answers prove you can use a PIVOT :)
查询没有PIVOT,即使其他答案证明你可以使用PIVOT :)
SELECT
MAX(DE.Text) AS DE,
MAX(EN.Text) AS EN
FROM TextTable AS TT
LEFT JOIN TextTable AS DE
ON DE.ID = TT.ID
AND DE.ISO = 'DE'
LEFT JOIN TextTable AS EN
ON EN.ID = TT.ID
AND EN.ISO = 'EN'
#4
1
If you try this solution and get a syntax error try setting the compatability mode of your database via
如果您尝试此解决方案并获得语法错误,请尝试通过设置数据库的兼容性模式
ALTER DATABASE myDatabase SET COMPATIBILITY_LEVEL = 90;
This will set the compatability to SQLServer 2005 and the above queries will execute w/o a syntax error.
这将设置SQLServer 2005的兼容性,上述查询将执行没有语法错误。
#5
1
select
t.num_claim_no,
rtrim (xmlagg (xmlelement (e, t.txt_remarks ||'@'|| t.dat_update_date || ' , ')).extract ('//text()'), ',') Remarks,
rtrim (xmlagg (xmlelement (e, t.num_update_no || ' , ')).extract ('//text()'), ',') SrlNo
from
gc_clm_gen_info t
where t.txt_remarks is not null
group by
t.num_claim_no
;
#6
0
Since you explicitly asked for a non-pivot solution: this should work, if you know which ISOs you will have in the rows. I called the table "Test".
由于您明确要求非透视解决方案:如果您知道行中将包含哪些ISO,这应该可行。我把表称为“测试”。
declare @temp table ([ID] int, [de] varchar(255), [en] varchar(255)) -- add ISOs if necessary
INSERT @temp
SELECT distinct [ID], '', '' from Test -- ADD '' for other ISOs if necessary
DECLARE c CURSOR read_only
FOR SELECT [ID], [ISO], [Text] from test
DECLARE @ID int, @ISO char(2), @Text varchar(255)
OPEN c
FETCH NEXT FROM c INTO @ID, @ISO, @Text
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
UPDATE @temp
SET [DE] = case when @ISO = 'DE' then @Text else [de] end,
[EN] = case when @ISO = 'EN' then @Text else [en] end
-- add ISOs if necessary
WHERE [ID] = @ID
END
FETCH NEXT FROM c INTO @ID, @ISO, @Text
END
CLOSE c
DEALLOCATE c
SELECT * FROM @temp