Need to edge out the competition for your dream job? Train for certifications today.
Monitor your entire network from a single platform. Free 30 Day Trial Now!
;with test_data as (
SELECT 0 col1, 2 col2, 3 col3, 0 col4 union all
SELECT 4 col1, 0 col2, 5 col3, 0 col4 union all
SELECT 2 col1, 5 col2, 5 col3, 0 col4 union all
SELECT 4 col1, 0 col2, 8 col3, 9 col4 union all
SELECT 0 col1, 6 col2, 0 col3, 7 col4
select , 
from (select id, col, row_number() over(partition by id order by colseq) as ky
from (select id, col, colseq
from (select row_number() over(order by col1) as id, col1 as , col2 as , col3 as , col4 as 
from test_data) v
unpivot(col for colseq in (,,,) ) as unpvt)x
where col <> 0 )y
pivot(max(col) for ky in (,))pvt
Open in new window
Topics: SQL Server 2008, Databases Miscellaneous, MySQL Server
select max(case when seq = 1 then col end) as "1",
max(case when seq = 2 then col end) as "2"
from (select id, col, if(@lastid=id,@i:=@i+1,@i:=1) as seq, @lastid:=id
from (select id,
case colseq when 1 then col1
when 2 then col2
when 3 then col3
when 4 then col4
end as col
from (select @id:=@id+1 as id, col1, col2, col3, col4
from test_data td,
(select @s:=@s+1 as colseq
from (select 1 x union all select 1 union all select 1 union all select 1)a,
-- (select 1 x union all select 1 union all select 1 union all select 1)b,
-- (select 1 x union all select 1 union all select 1 union all select 1)c,
order by id, colseq)z,
where col <> 0)f
group by id
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.