I am having SQL table where the records of the employee on daily basis are stored/saved I would like to get that result in tabular format as shown in attached screenshots i am using SQL 2012
hi
people on this forum will help you
if you post the data in create table insert data table format
i have done the hard part .. create data script
experts please do the easy part and help this guy
drop create data ...
/***************************************************************************/
-- drop all temp tables
DECLARE @SQL NVARCHAR(max) = (SELECT 'DROP TABLE ' + Stuff( ( SELECT ',' + NAME
FROM
tempdb.sys.tables FOR
xml path(
'') ), 1, 1, ''));
--PRINT @SQL;
EXEC Sp_executesql
@SQL;
/***************************************************************************/
-- create table
-- create table #data ( ) go ....
-- INT, VARCHAR(100), DATE, DATETIME, DECIMAL(10,2),
create table #data
(
ALMTIME DATETIME,
TAGNAME VARCHAR(100),
STATUS VARCHAR(100),
NORMSTS VARCHAR(100),
UNIT VARCHAR(100),
TEMP DECIMAL(10,2),
OPERNAME VARCHAR(100)
)
go
/***************************************************************************/
-- insert data into tables
-- insert into #data select
insert into #data select '2019-07-07 10:10:00','A','LO',' ','C ',18.5 ,'JOHN'
insert into #data select '2019-07-07 10:11:00','B','LO',' ','C ',14.2 ,'WILLIAM'
insert into #data select '2019-07-07 10:14:00','C','LO',' ','C ',14.36,'WILLIAM'
insert into #data select '2019-07-07 10:15:00','B','OK',' ','C ',12.01,'KATE'
insert into #data select '2019-07-07 10:17:00','A','OK',' ','F ',17 ,'JOHN'
insert into #data select '2019-07-07 10:18:00','A',' ','Y','C ',17.26,'JOHN'
insert into #data select '2019-07-07 10:19:00','D','LO',' ','PA',11.2 ,'KATE'
insert into #data select '2019-07-07 10:21:00','C','OK',' ','C ',22.21,'WILLIAM'
insert into #data select '2019-07-07 10:24:00','E','LO',' ','C ',27.5 ,'JOHN'
insert into #data select '2019-07-07 10:25:00','E','OK',' ','C ',28.1 ,'KATE'
insert into #data select '2019-07-07 10:27:00','D','OK',' ','PA',15.24,'JOHN'
insert into #data select '2019-07-07 10:29:00','B',' ','Y','C ',15.36,'WILLIAM'
insert into #data select '2019-07-07 10:30:00','D',' ','Y','F ',17.25,'JOHN'
go
/****************************************************************************/
-- select from all temp tables
DECLARE @SQL1 NVARCHAR(max) = (SELECT Stuff((SELECT '; select * from ' + NAME +
' go '
FROM tempdb.sys.tables
FOR xml path('')), 1, 1, ''));
--PRINT @SQL1;
EXEC Sp_executesql
@SQL1;
/***************************************************************************/
hi
i tried to do this ... hope it helps ... 
... i love any feedback
drop create data ...
/***************************************************************************/
-- drop all temp tables
DECLARE @SQL NVARCHAR(max) = (SELECT 'DROP TABLE ' + Stuff( ( SELECT ',' + NAME
FROM
tempdb.sys.tables FOR
xml path(
'') ), 1, 1, ''));
--PRINT @SQL;
EXEC Sp_executesql
@SQL;
/***************************************************************************/
-- create table
-- create table #data ( ) go ....
-- INT, VARCHAR(100), DATE, DATETIME, DECIMAL(10,2),
create table #data
(
ALMTIME DATETIME,
TAGNAME VARCHAR(100),
STATUS VARCHAR(100),
NORMSTS VARCHAR(100),
UNIT VARCHAR(100),
TEMP DECIMAL(10,2),
OPERNAME VARCHAR(100)
)
go
/***************************************************************************/
-- insert data into tables
-- insert into #data select
insert into #data select '2019-07-07 10:10:00','A','LO',' ','C ',18.5 ,'JOHN'
insert into #data select '2019-07-07 10:11:00','B','LO',' ','C ',14.2 ,'WILLIAM'
insert into #data select '2019-07-07 10:14:00','C','LO',' ','C ',14.36,'WILLIAM'
insert into #data select '2019-07-07 10:15:00','B','OK',' ','C ',12.01,'KATE'
insert into #data select '2019-07-07 10:17:00','A','OK',' ','F ',17 ,'JOHN'
insert into #data select '2019-07-07 10:18:00','A',' ','Y','C ',17.26,'JOHN'
insert into #data select '2019-07-07 10:19:00','D','LO',' ','PA',11.2 ,'KATE'
insert into #data select '2019-07-07 10:21:00','C','OK',' ','C ',22.21,'WILLIAM'
insert into #data select '2019-07-07 10:24:00','E','LO',' ','C ',27.5 ,'JOHN'
insert into #data select '2019-07-07 10:25:00','E','OK',' ','C ',28.1 ,'KATE'
insert into #data select '2019-07-07 10:27:00','D','OK',' ','PA',15.24,'JOHN'
insert into #data select '2019-07-07 10:29:00','B',' ','Y','C ',15.36,'WILLIAM'
insert into #data select '2019-07-07 10:30:00','D',' ','Y','F ',17.25,'JOHN'
go
/****************************************************************************/
-- select from all temp tables
DECLARE @SQL1 NVARCHAR(max) = (SELECT Stuff((SELECT '; select * from ' + NAME +
' go '
FROM tempdb.sys.tables
FOR xml path('')), 1, 1, ''));
--PRINT @SQL1;
EXEC Sp_executesql
@SQL1;
/***************************************************************************/
SQL ...
/***************************************************************************/
; WITH cte
AS (SELECT Row_number()
OVER(
partition BY tagname
ORDER BY tagname) AS rn,
*
FROM #data)
SELECT a.almtime AS [ALM IN],
b.almtime AS [ALM OUT],
a.tagname AS [TAG],
a.unit AS [UNIT],
a.temp AS [TEMP],
a.opername AS [OPERNAME],
c.almtime AS [ALM ACK TIME]
FROM (SELECT *
FROM cte a
WHERE rn = 1) a
JOIN (SELECT *
FROM cte b
WHERE rn = 2) b
ON a.tagname = b.tagname
LEFT JOIN (SELECT *
FROM cte b
WHERE rn = 3) c
ON a.tagname = c.tagname
/***************************************************************************/
