如何在 SQL Server 中水平显示数据? [英] How to display data horizontally in SQL Server?

问题描述

如何水平显示表格数据?

How do I display my table data horizontally?

这是我的表定义

create table [User]
(
    Id int primary key identity(1,1),
    Name varchar(50),
    Gender varchar(10)
)

这是我的 SQL Server 表中的数据

This is the data I have in my SQL Server table

+====+=======+========+
| Id | Name  | Gender |
+====+=======+========+
| 1  | Fahad | Male   |
+----+-------+--------+
| 2  | Saad  | Male   |
+----+-------+--------+
| 3  | Asif  | Male   |
+====+=======+========+

我想像这样水平显示

+========+=======+======+======+
| Id     | 1     | 2    | 3    |
+========+=======+======+======+
| Name   | Fahad | Saad | Asif |
+--------+-------+------+------+
| Gender | Male  | Male | Male |
+========+=======+======+======+

推荐答案

也许是 UNPIVOT 和 PIVOT 的组合?

Perhaps a combination of UNPIVOT and PIVOT?

(虽然你的列需要是相同的类型才能工作，我已经在你的表中改变了，或者你可以在 SELECT 中 CAST/CTE 等)

(Although your columns need to be of the same type for this to work, which I've changed in your table, or you can just CAST in a SELECT/CTE etc)

CREATE table [User](
Id int primary key identity(1,1),
Name varchar(50),
Gender varchar(50)
)

SET IDENTITY_INSERT [User] ON

INSERT INTO [User](Id,Name,Gender) VALUES 
(1, 'Fahad','Male'),
(2,'Saad','Male'),
(3,'Asif','Male')

SELECT * FROM [User]
UNPIVOT ([Value] FOR Cols IN ([Name],[Gender])) Unp
PIVOT   (MAX([Value]) FOR Id IN ([1],[2],[3])) Piv


Cols    1      2      3
------  ------ ------ -------
Gender  Male   Male   Male
Name    Fahad  Saad   Asif

(2 row(s) affected)

CASE 也可用于实现相同的目标 - SO 上有大量示例.

CASE can also be used to achieve the same - there are tons of examples on SO.

很好的例子 转置列和行的简单方法在 SQL 中?(这可能是那个问题的重复)

Excellent example Simple way to transpose columns and rows in Sql? (and this is probably a dup of that question)

这篇关于如何在 SQL Server 中水平显示数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！