在 SQL Server 中将行转换为列 [英] Converting rows to column in SQL Server

问题描述

我有一个表 user_travel，其中包含此示例数据:

I have a table user_travel with this sample data in it:

我想要一个查询，在每个 user ID 的 Visit 列的帮助下返回如下所示的输出..

I want a query which returns the output shown below, with the help of the Visit column for each user ID..

预期输出

id  FromD-1visit ToD-1visit  FromD-2visit ToD-2visit  FromD-3visit ToD-3visit  UserID
-------------------------------------------------------------------------------------
1    2017-05-17  2017-05-17  2016-02-02      NULL      2016-02-01  2016-02-09    2

这里的FromD 和ToD 是FromDate 和ToDate (columns)

我也尝试过使用 PIVOT 但没有成功.

I've also tried using PIVOT but without luck.

请帮忙..！

提前致谢...

推荐答案

这个脚本是动态的.

CREATE TABLE #tt(Id INT,FromDate DATETIME,ToDate DATETIME,Visit INT,UserID INT);
INSERT INTO #tt(Id,FromDate,ToDate,Visit,UserID)VALUES
    (1,'20170517','20170517',1,2),
    (6,'20160202',NULL,2,2),
    (7,'20160201','20160209',3,2),
    (8,'20160207','20160201',NULL,3);

-- select distinct Visit ids
SELECT DISTINCT
    Visit
INTO
    #visit_ids
FROM
    #tt
WHERE
    Visit IS NOT NULL;

-- create dynamic select columns (forcing order by Visit id)
DECLARE @sel_cols NVARCHAR(MAX)=STUFF((
    SELECT
        ',[FromD-'+CAST(Visit AS VARCHAR(3))+'visit]=MAX(CASE WHEN Visit='+CAST(Visit AS VARCHAR(3))+' THEN FromDate END)'+
        ',[ToD-'+CAST(Visit AS VARCHAR(3))+'visit]=MAX(CASE WHEN Visit='+CAST(Visit AS VARCHAR(3))+' THEN ToDate END)'
    FROM
        #visit_ids
    ORDER BY
        Visit
    FOR
        XML PATH('')
    ),1,1,''
);

DECLARE @stmt NVARCHAR(MAX)=N'
    SELECT
        id=ROW_NUMBER() OVER (ORDER BY UserId),
        UserId,'+
        @sel_cols+'
    FROM
        #tt
    WHERE
        Visit IS NOT NULL
    GROUP BY
        UserId;
';

EXECUTE sp_executesql @stmt;

DROP TABLE #visit_ids;
DROP TABLE #tt;

结果:

+----+--------+-------------------------+-------------------------+-------------------------+------------+-------------------------+-------------------------+
| id | UserId |      FromD-1visit       |       ToD-1visit        |      FromD-2visit       | ToD-2visit |      FromD-3visit       |       ToD-3visit        |
+----+--------+-------------------------+-------------------------+-------------------------+------------+-------------------------+-------------------------+
|  1 |      2 | 2017-05-17 00:00:00.000 | 2017-05-17 00:00:00.000 | 2016-02-02 00:00:00.000 | NULL       | 2016-02-01 00:00:00.000 | 2016-02-09 00:00:00.000 |
+----+--------+-------------------------+-------------------------+-------------------------+------------+-------------------------+-------------------------+

这篇关于在 SQL Server 中将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！