Test data table - user_traveldetails
ID FromDate ToDate UserId
1 1/1/2014 1/10/2014 44
2 2/1/2014 3/10/2014 44
3 1/1/2015 2/10/2015 44
Expected Output:
FromDate1 ToDate1 FromDate2 ToDate2 FromDate3 ToDate3 UserId
1/1/2014 1/10/2014 2/1/2014 3/10/2014 1/1/2015 2/10/2015 44
I required fromdate and todate in to the single row.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT top 12 ',' + quotename(fromdate) FROM User_TravelDetails group by(fromdate) order by MAX(fromdate) desc FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
set @query = 'Select * from (select id as fromid, email as fromemail, ' + @cols + ' from ( SELECT * FROM
(select top 10000 A.ID,Email,userid,FromDate,visit from UserMaster A
inner join User_TravelDetails B
on A.ID= B.USerid order by B.FromDate
) as s PIVOT
( MIN(FromDate) FOR [FromDate] IN (' + @cols + ')
)AS pvt
) as X)
P join
(Select id as toid, email as toemail,' + @cols +
'from(SELECT * FROM (select top 10000
A.ID,Email,userid,toDate,visit from UserMaster A
inner join
User_TravelDetails B on A.ID= B.USerid order by B.FromDate ) as s
PIVOT(MIN(toDate) FOR [toDate] IN (' + @cols + ') )AS pvt2
) as Y ) Q on P.fromemail=Q.toemail'
print @query EXEC
SP_EXECUTESQL @query
Please help on the query. Thanks
