DECLARE @strSQL NVARCHAR(MAX)
DECLARE @Date_Conv VARCHAR(MAX)
DECLARE @Cost NVARCHAR(2)
SET @Cost = '30'
SET @Date_Conv = STUFF((SELECT DISTINCT ',[' + [Date_Conv] + ']' FROM V_SubQuery FOR XML PATH('')),1,1,'')
SET @strSQL = 'SELECT SSN AS PersonID,Name AS Name_Lastname, ' + @Date_Conv + ' FROM V_SubQuery PIVOT (MIN(Time_Conv) FOR [Date_Conv] IN (' + @Date_Conv + ')) AS pvt WHERE pvt.OPHONE='+ @Cost +''
exec SP_EXECUTESQL @strSQL
DECLARE @Big TABLE (
FullName VARCHAR(255),
Date_Conv DATETIME,
Time_Conv VARCHAR(5)
)
INSERT INTO @Big VALUES ('Jim','2013-09-01','08:02')
INSERT INTO @Big VALUES ('Jim','2013-09-01','16:50')
INSERT INTO @Big VALUES ('Jim','2013-09-02','08:04')
INSERT INTO @Big VALUES ('Jim','2013-09-02','16:25')
INSERT INTO @Big VALUES ('Jim','2013-09-03','08:29')
INSERT INTO @Big VALUES ('Jim','2013-09-03','16:02')
SELECT *
FROM @Big
Pivot(Min(Time_Conv) FOR Date_Conv IN (
[2013-09-01],
[2013-09-02],
[2013-09-03]
)) AS Pvt1
UNION ALL
SELECT *
FROM @Big
Pivot(Max(Time_Conv) FOR Date_Conv IN (
[2013-09-01],
[2013-09-02],
[2013-09-03]
)) AS Pvt2
Load balance : Server 00
