如何使用Pivot显示列数据 [英] How Can I Show The Columns Data Using Pivot

问题描述

我有一个包含多列的表格。我需要将每列显示为单行本身。包括id。

我的表：

Id ||名称|| Desc ||评论

1 || ABC || abcd || abcdab

2 || efg || adkfj || ajfkjdsalk

3 || aaa || dklasf || kdfjdkjfkjdk

等等

我想在单行中显示每一列。

需求输出：

id1 || id2 || id3 || < u> Name1 || Name2 || Name3 || Comeents1 || comments2 || < u> comments3

1 || 2 || 3 || abc || efg || aaa || abcdab || ajfkjdsalk || kdfjdkjfkjdk



我试过使用数据透视但无法显示此输出。你能否建议我使用pivot是一种合适的技术还是以另一种方式显示数据。？

-

先谢谢你。 ：）

Hi,
I have a table with multiple columns. For which i need to show every column into single row itself. including the id.
my table:
Id|| Name ||Desc || comments
1 || abc|| abcd || abcdab
2 || efg || adkfj || ajfkjdsalk
3 || aaa || dklasf || kdfjdkjfkjdk
so on
i want to show every column in single row.
Req Output:
id1 ||id2 ||id3 ||Name1 ||Name2 ||Name3 ||Comeents1 ||comments2 ||comments3
1 || 2 || 3 || abc || efg || aaa || abcdab || ajfkjdsalk || kdfjdkjfkjdk

i have tried using pivot but unable to display this output. Can you suggest me if pivot is an appropriate technique to use or is there someother way to display the data.?
--
Thanks in Advance. :)

推荐答案

假设这是我们的表：

Assume this is our table:

CREATE TABLE dbo.SomeTable
(
	Id INT NOT NULL,
	Name VARCHAR(100) NOT NULL,
	[Description] NVARCHAR(max) NULL,
	Comments NVARCHAR(max) NULL,
	CONSTRAINT PK_SomeTable PRIMARY KEY (Id)
);

如果行数和列数始终是固定的，则此示例将起作用：

If the number of rows and columns is always fixed, this example will work:

DECLARE @flatenedTable TABLE
(
	Id_1 INT NULL,
	Id_2 INT NULL,
	Id_3 INT NULL,
	Id_4 INT NULL,
	Name_1 VARCHAR(100) NULL,
	Name_2 VARCHAR(100) NULL,
	Name_3 VARCHAR(100) NULL,
	Name_4 VARCHAR(100) NULL,
	Description_1 VARCHAR(MAX) NULL,
	Description_2 VARCHAR(MAX) NULL,
	Description_3 VARCHAR(MAX) NULL,
	Description_4 VARCHAR(MAX) NULL,
	Comments_1 VARCHAR(MAX) NULL,
	Comments_2 VARCHAR(MAX) NULL,
	Comments_3 VARCHAR(MAX) NULL,
	Comments_4 VARCHAR(MAX) NULL
);

DECLARE @startingId INT

SELECT
	@startingId = MIN(Id)
FROM dbo.SomeTable

INSERT INTO @flatenedTable (
	Id_1,
	Id_2,
	Id_3,
	Id_4,
	Name_1,
	Name_2,
	Name_3,
	Name_4,
	Description_1,
	Description_2,
	Description_3,
	Description_4,
	Comments_1,
	Comments_2,
	Comments_3,
	Comments_4
)
SELECT
	CASE WHEN Id = @startingId + 0 THEN Id ELSE NULL END AS Id_1,
	CASE WHEN Id = @startingId + 1 THEN Id ELSE NULL END AS Id_2,
	CASE WHEN Id = @startingId + 2 THEN Id ELSE NULL END AS Id_3,
	CASE WHEN Id = @startingId + 3 THEN Id ELSE NULL END AS Id_4,
	CASE WHEN Id = @startingId + 0 THEN Name ELSE NULL END AS Name_1,
	CASE WHEN Id = @startingId + 1 THEN Name ELSE NULL END AS Name_2,
	CASE WHEN Id = @startingId + 2 THEN Name ELSE NULL END AS Name_3,
	CASE WHEN Id = @startingId + 3 THEN Name ELSE NULL END AS Name_4,
	CASE WHEN Id = @startingId + 0 THEN [Description] ELSE NULL END AS Description_1,
	CASE WHEN Id = @startingId + 1 THEN [Description] ELSE NULL END AS Description_2,
	CASE WHEN Id = @startingId + 2 THEN [Description] ELSE NULL END AS Description_3,
	CASE WHEN Id = @startingId + 3 THEN [Description] ELSE NULL END AS Description_4,
	CASE WHEN Id = @startingId + 0 THEN [Comments] ELSE NULL END AS Comments_1,
	CASE WHEN Id = @startingId + 1 THEN [Comments] ELSE NULL END AS Comments_2,
	CASE WHEN Id = @startingId + 2 THEN [Comments] ELSE NULL END AS Comments_3,
	CASE WHEN Id = @startingId + 3 THEN [Comments] ELSE NULL END AS Comments_4
FROM dbo.SomeTable

SELECT
	MAX(Id_1) AS Id_1,
	MAX(Id_2) AS Id_2,
	MAX(Id_3) AS Id_3,
	MAX(Id_4) AS Id_4,
	MAX(Name_1) AS Name_1,
	MAX(Name_2) AS Name_2,
	MAX(Name_3) AS Name_3,
	MAX(Name_4) AS Name_4,
	MAX(Description_1) AS Description_1,
	MAX(Description_2) AS Description_2,
	MAX(Description_3) AS Description_3,
	MAX(Description_4) AS Description_4,
	MAX(Comments_1) AS Comments_1,
	MAX(Comments_2) AS Comments_2,
	MAX(Comments_3) AS Comments_3,
	MAX(Comments_4) AS Comments_4
FROM @flatenedTable

但是，如果它们没有修复，你需要使用动态SQL来实现这个目的：

However, if they are not fixed, you will need to used dynamic SQL to achieve this:

DECLARE @numberOfRows INT;
DECLARE @index INT = 1;
DECLARE @newLine CHAR(2) = CHAR(13) + CHAR(10);

SELECT
	@numberOfRows = COUNT(*)
FROM dbo.SomeTable

DECLARE @variableTableSql NVARCHAR(MAX) = N'DECLARE @flatenedTable TABLE' + @newLine + '(' + @newLine;
DECLARE @selectStatement NVARCHAR(MAX) = N'SELECT' + @newLine;
DECLARE @aggregateSelect NVARCHAR(MAX) = N'SELECT' + @newLine;

WHILE @index <= @numberOfRows
BEGIN
	SET @variableTableSql = CONCAT(@variableTableSql,
		CHAR(9), N'Id_', @index, N' INT NULL,', @newLine,
		CHAR(9), N'Name_', @index, N' VARCHAR(100) NULL,', @newLine,
		CHAR(9), N'Description_', @index, N' VARCHAR(MAX) NULL,' + @newLine,
		CHAR(9), N'Comments_', @index, N' VARCHAR(MAX) NULL');

	SET @selectStatement = CONCAT(@selectStatement,
		CHAR(9), N'CASE WHEN Id = ', @index, N' THEN Id ELSE NULL END AS Id_', @index, N',', @newLine,
		CHAR(9), N'CASE WHEN Id = ', @index, N' THEN Name ELSE NULL END AS Name_', @index, N',', @newLine,
		CHAR(9), N'CASE WHEN Id = ', @index, N' THEN [Description] ELSE NULL END AS Description_', @index, N',', @newLine,
		CHAR(9), N'CASE WHEN Id = ', @index, N' THEN [Comments] ELSE NULL END AS Comments_', @index);

	SET @aggregateSelect = CONCAT(@aggregateSelect,
		CHAR(9), 'MAX(Id_', @index, N') AS Id_', @index, N',', @newLine,
		CHAR(9), 'MAX(Name_', @index, N') AS Name_', @index, N',', @newLine,
		CHAR(9), 'MAX(Description_', @index, N') AS Description_', @index, N',', @newLine,
		CHAR(9), 'MAX(Comments_', @index, N') AS Comments_', @index);

	IF @index != @numberOfRows
	BEGIN
		SET @variableTableSql = CONCAT(@variableTableSql, N',', @newLine);
		SET @selectStatement = CONCAT(@selectStatement, N',', @newLine);
		SET @aggregateSelect = CONCAT(@aggregateSelect, N',', @newLine);
	END
	ELSE
	BEGIN
		SET @variableTableSql = CONCAT(@variableTableSql, @newLine, ');');
		SET @selectStatement = CONCAT(@selectStatement, @newLine, 'FROM dbo.SomeTable');
		SET @aggregateSelect = CONCAT(@aggregateSelect, @newLine, 'FROM @flatenedTable');
	END

	SET @index = @index + 1
END

--DEBUG purposes
--PRINT @variableTableSql;
--PRINT @selectStatement;

DECLARE @sql NVARCHAR(MAX);

SET @sql = CONCAT(
	@variableTableSql,
	@newLine,
	@newLine,
	'INSERT INTO @flatenedTable',
	@newLine,
	@selectStatement,
	@newLine,
	@newLine,
	@aggregateSelect);

--DEBUG purpose
--PRINT @sql

EXEC sp_executesql @sql

这篇关于如何使用Pivot显示列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！