January 5, 2012 at 11:51 am
Hello all,
Is there any other way to write this code with out using union's?
As there are more then 10 types, I would like to know If I can avoid using union's.
use tempdb
go
declare @testlvl table(
[ty_3] [varchar](2),
[ty_3_dsc] [varchar](20),
[ty_3_cd] [varchar](20),
[ty_2] [varchar](2),
[ty_2_dsc] [varchar](20),
[ty_2_cd] [varchar](20),
[ty_1] [varchar](2),
[ty_1_dsc] [varchar](20),
[ty_1_cd] [varchar](20),
[somefield] [varchar](30),
[description] [varchar](20),
[flag] [numeric](1, 0),
[name] [varchar](20),
[somecode] [varchar](20)
)
insert into @testlvl
([ty_3],
[ty_3_dsc],
[ty_3_cd],
[ty_2],
[ty_2_dsc] ,
[ty_2_cd],
[ty_1] ,
[ty_1_dsc] ,
[ty_1_cd] ,
[somefield] ,
[description] ,
[flag] ,
[name],
[somecode])
values ( null, 'lvl3', '3',
null, 'lvl2', '2', null, 'lvl1', '1',
'somefield', 'description',1, 'name', 'somecode')
--select * from @testlvl
select
[somefield] ,
[description] ,
[flag] ,
[name],
[somecode],
[ty_1] as [type],
[ty_1_dsc] as [desc] ,
[ty_1_cd] as [cd]
from @testlvl
Union
select
[somefield] ,
[description] ,
[flag] ,
[name],
[somecode],
[ty_2] as [type],
[ty_2_dsc] as [desc] ,
[ty_2_cd] as [cd]
from @testlvl
union
select
[somefield] ,
[description] ,
[flag] ,
[name],
[somecode],
[ty_3] as [type],
[ty_3_dsc] as [desc] ,
[ty_3_cd] as [cd]
from @testlvl
Thanks in advance!
January 5, 2012 at 12:08 pm
You can use UNPIVOT, but that gets very messy when you're trying to unpivot into more than one column. You would need three.
You can also use CROSS APPLY to simplify the statement that you have. Using that method, you only have to specify the shared columns once.
SELECT SomeField, [Description], Flag, [Name], SomeCode
,[Type], [Desc], Cd
FROM @TestLvl
CROSS APPLY (
SELECT [ty_1] as [type],
[ty_1_dsc] as [desc] ,
[ty_1_cd] as [cd]
UNION
SELECT
[ty_2] as [type],
[ty_2_dsc] as [desc] ,
[ty_2_cd] as [cd]
UNION
SELECT [ty_3] as [type],
[ty_3_dsc] as [desc] ,
[ty_3_cd] as [cd]
) AS Lvl
Drew
PS: I realize that this is only a sample table structure, but you should avoid using SQL Keywords as column names whenever possible. These include Description, Name, Type, and Desc.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 5, 2012 at 12:16 pm
Thank you Drew!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply