Convert Every Other Row into columns

Transact-SQL
1

I have a table. I would like to convert it in SQL to look like the table in the picture: Basically, I need to move every other row to additional columns.
How would you do this?

image

2

hi i have tried to do this

hope it helps

:slight_smile:
:slight_smile:

drop create data ... 
drop table data 
go 

create table data (time1 datetime, tagid int ,stringvalue varchar(100))

insert into data 
values 
('2/9/2019 19:06:09',500,'20945030'),
('2/9/2019 17:54:13',500,'   '),
('2/9/2019 16:02:26',500,'20946065'),
('2/9/2019 16:02:00',500,'   '),     
('2/9/2019 16:01:07',500,'20936368'),
('2/9/2019 15:29:13',500,'   ') ,
('2/9/2019 14:58:15',500,'20934130'),
('2/9/2019 14:57:58',500,'   ') 
go 

select * from data 
go
SQL ... 
; WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY time1) AS rn, 
                * 
         FROM   data) 
SELECT b.time1, 
       b.tagid, 
       b.stringvalue, 
       a.time1, 
       a.tagid, 
       b.stringvalue 
FROM   cte a 
       JOIN cte b 
         ON a.rn + 1 = b.rn 
WHERE  b.stringvalue <> '   ' 

go
Result

image
image.png836×190 6.22 KB

3

Thank you so much. I came up with similar solution:

select Dateadd (s,t_stamp/1000, '1969/12/31 20:00:00') AS TimeStamp,a.tagid,a.stringvalue

From sqlt_data_5_2019_02 a
join sqlth_te b
ON b.id = a.tagid
where tagpath in ('System1')

WITH cte AS (
SELECT Dateadd (s,t_stamp/1000, '1969/12/31 20:00:00') AS TimeStamp,a.tagid, a.stringvalue,
ROW_NUMBER() OVER (ORDER BY t_stamp) - 1 AS rn
FROM sqlt_data_5_2019_02 a
JOIN sqlth_te b
ON b.id = a.tagid
where tagpath in ('System1')
)
SELECT
MIN(TimeStamp) AS StartTime,
MAX(TimeStamp) AS EndTime,
MAX(tagid) AS tagid,
MAX(stringvalue) AS stringvalue,
MAX(tagid) AS tagid,
MAX(stringvalue) AS stringvalue
FROM cte
GROUP BY rn / 2;

4

Which version of SQL? For SQL 2012 and later, you can use LEAG/LAG to do this much more efficiently.