如何在SQL中转置表格 – 是UNPIVOT是唯一的选项吗?

huangapple go评论70阅读模式
英文:

How to transpose a table in SQL - is UNPIVOT the only option?

问题

我有一个使用以下方案的SQL表格

[A路径] [B路径] [C路径] [D路径]

我想要使用一些where子句查询表格(它将匹配一行),并以表格形式呈现结果。

所以如果匹配的行是

A路径 B路径 C路径 D路径
a值 b值 c值 d值

我希望呈现结果如下

文件 路径
A a值
B b值
C c值
D d值

我尝试使用SQL UNPIVOT,但找不到正确的语法,我还怀疑UNPIVOT不会满足我的需求,因为它需要一些聚合函数。如果我错了,请纠正我。

英文:

I have an SQL table using this scheme

[A Path] [B Path] [C Path] [D Path]

I want to query the table with some where clause (it will match a single row) and present the result in a tabular form.

so if the matched row is

A Path B Path C Path D Path
aValue bValue cValue dValue

I wish to present the results as

File Path
A aValue
B bValue
C cValue
D dValue

I tried using SQL UNPIVOT but could not find the right syntax, I also suspect UNPIVOT will not serve my purpose as it needs some aggregation function. correct me if I'm wrong.

答案1

得分: 3

你的“unpivot”必须像这样:

但是这种方法会为每一行生成一个新的行,这些行都是具有不同路径的相同文件,所以你可能要检查一下这是否是你真正想要的。

CREATE TABLE mytable
    ([A Path] varchar(6), [B Path] varchar(6), [C Path] varchar(6), [D Path] varchar(6))
;
    
INSERT INTO mytable
    ([A Path], [B Path], [C Path], [D Path])
VALUES
    ('aValue', 'bValue', 'cValue', 'dValue')
;
SELECT  [File], [Path]  
FROM   
   (SELECT [A Path] as A, [B Path] as B, [C Path] as C, [D Path] AS D FROM  mytable ) t
UNPIVOT  
   ([Path] FOR [File] IN   
      (A,  B,  C,  D)  
)AS unpvt  
File Path
A aValue
B bValue
C cValue
D dValue

fiddle

英文:

Your unpivot must be like this

But this approach will give for every row a new row, that are the same file with different path, so you might chekcif that is really what yut want

CREATE TABLE mytable
    ([A Path] varchar(6), [B Path] varchar(6), [C Path] varchar(6), [D Path] varchar(6))
;
    
INSERT INTO mytable
    ([A Path], [B Path], [C Path], [D Path])
VALUES
    ('aValue', 'bValue', 'cValue', 'dValue')
;

1 rows affected
SELECT  [File], [Path]  
FROM   
   (SELECT [A Path] as A, [B Path] as B, [C Path] as C, [D Path] AS D FROM  mytable ) t
UNPIVOT  
   ([Path] FOR [File] IN   
      (A,  B,  C,  D)  
)AS unpvt  
File Path
A aValue
B bValue
C cValue
D dValue

fiddle

答案2

得分: 1

你可以使用 UNION ALL

选择 'A' 作为文件, a_path 作为路径 from mytable
union all
选择 'B' 作为文件, b_path 作为路径 from mytable
union all
选择 'C' 作为文件, c_path 作为路径 from mytable
union all
选择 'D' 作为文件, d_path 作为路径 from mytable;
英文:

You can use UNION ALL:

select 'A' as file, a_path as path from mytable
union all
select 'B' as file, b_path as path from mytable
union all
select 'C' as file, c_path as path from mytable
union all
select 'D' as file, d_path as path from mytable;

答案3

得分: 1

CROSS APPLY (VALUES 往往比 UNPIVOT 更清晰

选择
  v.文件,
  v.路径
从 你的表 t
交叉应用 (数值
  ('A', t.A_路径),
  ('B', t.B_路径),
  ('C', t.C_路径),
  ('D', t.D_路径)
) v(文件, 路径);
英文:

CROSS APPLY (VALUES is often far clearer than UNPIVOT

SELECT
  v.File,
  v.Path
FROM YourTable t
CROSS APPLY (VALUES
  ('A', t.A_path),
  ('B', t.B_path),
  ('C', t.C_path),
  ('D', t.D_path)
) v(file, path);

答案4

得分: 0

以下是您要翻译的内容:

只是另一种选项,可以动态地将几乎任何表、视图或即席查询解构,而无需使用动态 SQL 或列举所有列。

示例

Select A.[Id]
      ,Item  = B.[Key]
      ,Value = B.[Value]
 From  YourTable A
 Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] not in ('Id','Other','Columns','ToExclude')
             ) B
英文:

Just another option that will dynamically UNPIVOT virtually any table, view or ad-hoc query without having to use Dynamic SQL or enumerate all the columns.

Example

Select A.[Id]
      ,Item  = B.[Key]
      ,Value = B.[Value]
 From  YourTable A
 Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] not in ('Id','Other','Columns','ToExclude')
             ) B

huangapple
  • 本文由 发表于 2023年6月22日 05:58:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/76527413.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定