CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user5426326
user5426326

Reputation:

Converting rows to column in SQL Server

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

enter image description here

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

Expected output 

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

Here FromD and ToD are FromDate and ToDate (columns)

I've also tried using PIVOT but without luck.

Please help..!

Thanks in advance...

Upvotes: 0

Views: 71

Answers (2)

TT.
TT.

Reputation: 16137

This script does is dynamically.

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;

Result:

+----+--------+-------------------------+-------------------------+-------------------------+------------+-------------------------+-------------------------+
| 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 |
+----+--------+-------------------------+-------------------------+-------------------------+------------+-------------------------+-------------------------+

Upvotes: 1

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this

SQL Fiddle

select 
    f1.ID, f1.FromDate as 'FromD-1visit', 
    f1.ToDate as 'ToD-1visit',
    f2.FromDate as 'FromD-2visit', f2.ToDate as 'ToD-2visit',
    f3.FromDate as 'FromD-3visit', f3.ToDate as 'ToD-1visit',
    f1.UserID
from
    user_travel f1
left join  
    user_travel f2 on f2.UserID = f1.UserID and f2.Visit = 2
left join  
    user_travel f3 on f3.UserID = f1.UserID and f3.Visit = 3
where 
    f1.Visit = 1 and f1.UserID = 2

Upvotes: 0

Related Questions