英文:
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 |
英文:
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 |
答案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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论