SQLTeam.com | Weblogs | Forums

SQL query to display vertical data horizontalyy with another columns value

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 ... :slight_smile: :slight_smile: ... 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 

			  
/***************************************************************************/