sql-server 将行转换为具有动态列名称的列

lsmd5eda 于 2022-10-31 发布在其他

关注(0)|答案(1)|浏览(155)

我有两个表，一个包含列名，另一个包含相关数据。我尝试将行动态转换为列。目前我认为所有列都将包含在最终输出中，但在实际情况下，列名将基于一些预定义的输入。

DROP TABLE IF EXISTS dbo.TempTableData
go
DROP TABLE IF EXISTS dbo.TempTableColumn
go

CREATE TABLE dbo.TempTableColumn
(
    ColumnId int CONSTRAINT PK_ColumnId PRIMARY KEY
    ,ColumnName varchar(512) NOT NULL
)
go
CREATE NONCLUSTERED INDEX IX_TempTableColumn_ColumnName ON TempTableColumn(ColumnName) INCLUDE (ColumnId)
go

INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(1,'EmpId');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(2,'FirstName');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(3,'MiddleName');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(4,'LastName');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(5,'Age');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(6,'Gender');
go

CREATE TABLE dbo.TempTableData
(
    DataId int IDENTITY(1,1) CONSTRAINT PK_DataId PRIMARY KEY
    ,GroupId int NOT NULL
    ,ColumnId int NOT NULL CONSTRAINT FK_TempTableData_ColumnId REFERENCES TempTableColumn(ColumnId)
    ,ColumnValue varchar(8000)
)
go

INSERT INTO TempTableData(GroupId,ColumnId,ColumnValue) VALUES(101,1,'101'),(101,2,'John'),(101,4,'Grath'),(101,5,'40'),(101,6,'Male');
INSERT INTO TempTableData(GroupId,ColumnId,ColumnValue) VALUES(102,1,'102'),(102,2,'Smantha'),(102,4,'Fox'),(102,5,'35'),(102,6,'Female');
INSERT INTO TempTableData(GroupId,ColumnId,ColumnValue) VALUES(103,1,'103'),(103,2,'John'),(103,3,'M.'),(103,4,'Chang'),(103,5,'33'),(103,6,'Male');
go

DECLARE @cols AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(ColumnName) 
                    FROM TempTableColumn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
--SELECT @cols [ColumnName]

DECLARE @query AS NVARCHAR(MAX)
SET @query = N'SELECT GroupId,' + @cols + N' FROM 
             (
                SELECT td.GroupId,tc.ColumnName,td.ColumnValue
                FROM TempTableData td (nolock)
                    INNER JOIN TempTableColumn tc (nolock) ON (td.ColumnId = tc.ColumnId)
            ) x
            PIVOT
            (
                min(ColumnValue)
                FOR ColumnName in (' + @cols + N')
            ) p'
--SELECT @query
EXEC sp_executesql @query

我尝试了上述方法，但最终输出不包括所有记录。

我不认为PIVOT / UNPIVOT是这个问题的解决方案。如果有人能提供一些如何处理它的输入，我将不胜感激。
添加新列GroupId后

sql-server

来源：https://stackoverflow.com/questions/74219900/convert-rows-to-columns-with-dynamic-column-names

1条答案

按热度按时间

gzszwxb41#

首先，正如@Larnu所说，在代码中避免使用NOLOCK。其次，正如@Larnu所说，数据之间没有任何联系...
在理想情况下，数据链接是表中的接近度，基于DataId，您可以使用以下脚本来获得所需的结果：

DROP TABLE IF EXISTS dbo.TempTableData
GO
DROP TABLE IF EXISTS dbo.TempTableColumn
GO

CREATE TABLE dbo.TempTableColumn
(
    ColumnId int CONSTRAINT PK_ColumnId PRIMARY KEY
    ,ColumnName varchar(512) NOT NULL
)
GO
--CREATE NONCLUSTERED INDEX IX_TempTableColumn_ColumnName ON TempTableColumn(ColumnName) INCLUDE (ColumnId)
--GO

INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(1,'EmpId');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(2,'FirstName');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(3,'MiddleName');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(4,'LastName');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(5,'Age');
INSERT INTO TempTableColumn(ColumnId,ColumnName) VALUES(6,'Gender');
GO

CREATE TABLE dbo.TempTableData
(
    DataId int IDENTITY(1,1) CONSTRAINT PK_DataId PRIMARY KEY
    ,ColumnId int NOT NULL CONSTRAINT FK_TempTableData_ColumnId REFERENCES TempTableColumn(ColumnId)
    ,ColumnValue varchar(8000)
)
GO

INSERT INTO TempTableData(ColumnId,ColumnValue) VALUES(1,'101'),(2,'John'),(4,'Grath'),(5,'40'),(6,'Male');
INSERT INTO TempTableData(ColumnId,ColumnValue) VALUES(1,'102'),(2,'Smantha'),(4,'Fox'),(5,'35'),(6,'Female');
GO

--SELECT * FROM TempTableColumn
--SELECT * FROM TempTableData

DECLARE @cols AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(ColumnName) 
                    from TempTableColumn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
--SELECT @cols [ColumnName]
;
DECLARE @sql VARCHAR(MAX);
DECLARE @nb_col INT
DECLARE @i INT = 0
DECLARE @col_name VARCHAR(MAX)
SELECT @nb_col = COUNT(*) FROM TempTableColumn;

SET @sql =
'
WITH TMP ([Row],[ColumnId],[ColumnValue]) AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY [ColumnId] ORDER BY [DataId]),[ColumnId],[ColumnValue] FROM TempTableData
)
SELECT'

WHILE @i < @nb_col
BEGIN
    SELECT @col_name = ColumnName FROM TempTableColumn WHERE ColumnId = @i+1
    IF @i != 0
        SET @sql = @sql + ','
    SET @sql = @sql + ' TMP' + CONVERT(VARCHAR,@i+1) + '.ColumnValue AS ' + @col_name
    SET @i = @i + 1
END

SET @i = 0
WHILE @i < @nb_col
BEGIN
    IF @i = 0
        SET @sql = @sql +
'
FROM TMP TMP1'
    ELSE
        SET @sql = @sql +
'
LEFT JOIN TMP TMP' + CONVERT(VARCHAR,@i+1) + ' ON TMP' + CONVERT(VARCHAR,@i+1) + '.[Row] = TMP1.[Row] AND TMP' + CONVERT(VARCHAR,@i+1) + '.ColumnId = ' + CONVERT(VARCHAR,@i+1)
    SET @i = @i + 1
END
SET @sql = @sql +
'
WHERE TMP1.ColumnId = 1'

PRINT(@sql)

EXEC(@sql)

输出：
| 员工ID|名字|中间Name|姓氏|年龄|性别问题|
| - -|- -|- -|- -|- -|- -|
| 一百零一|若翰|空值|格拉特|四十个|男性|
| 一百零二|斯曼塔|空值|狐狸|三十五个|女性|

赞(0）回复(0）举报 2022-10-31

我来回答

sql-server 将行转换为具有动态列名称的列

1条答案

相关问题

热门标签

最新问答