Convert repeating blocks of columns into rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert repeating blocks of columns into rows

Author

Topic

gunnpat
Starting Member

1 Post

Posted - 2015-02-17 : 13:06:18

Hi,

Hope someone can help me with writing SQL code to convert blocks of columns into rows.

Example,
Id A1 A2 B1 B2
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3

into
Id Group Value
1 A 1
1 A 1
1 B 1
1 B 1
2 A 2
2 A 2
2 B 2
2 B 2

Thanks.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-17 : 14:51:59

use "union all"

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 17:56:12

[code]
declare @t table(Id int, A1 int, A2 int, B1 int, B2 int)
insert into @t ( Id, A1, A2, B1, B2 ) values
(1,1,1,1,1),
(2,2,2,2,2),
(3,3,3,3,3)

select id, grp, val
from @t t
cross apply
(
select * from (values
('A', A1),
('A', A2),
('B', B1),
('B', B2)
) v(grp,val)
) _
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources