January 11, 2012 at 11:17 pm
I have a tough programming problem in SQL Server 2000 that I'm hoping I can get help with. I am a novice with T-SQL (I mostly use MySQL) so maybe someone could look at this and see an easy way to do it.
I have a table with lots of columns that contain either a 0 or 1. For example, I have columns labeled var1, var2, var5, var9. etc. up to about 120 columns total. Notice that the suffixes on these columns aren't necessarily consecutive. Each of these columns contain a 0 or 1. What I need to do is create a new column the contains the column names of every column that contains a 1.
For example, my data look like this:
ID var1 var2 var5 var7 var9
1 0 1 0 0 1
2 0 1 1 1 0
I need a new column that contains the values
ID NewCol
1 var2,var9
2 var2,var5,var7
and so on.
For reasons I won't go in to, I can't use any client side code like VB, Perl, etc. I have to do this in a stored procedure. I feel really limited by my T-SQL knowledge. Can this be done??
TIA
January 13, 2012 at 7:04 am
--create table variable
declare @Table table(Id int identity(1,1), Var1 bit, Var2 bit, Var5 bit, Var7 bit, Var9 bit)
insert into @Table(Var1, Var2, Var5, Var7, Var9)
select 0, 1, 0, 0, 1
union all
select 0, 1, 1, 1, 0
--store intermediate result
declare @Values table(Id int, Col varchar(100))
insert into @Values(Id, Col)
select
ID,Col
from
(select * from @Table) p
unpivot
(Val for Col in (Var1,Var2,Var5,Var7,Var9)) as unpvt
where
Val=1
--concatenate and strip trailing comma
select
Id,
left(Columns,len(Columns)-1) as Columns
from
(
select
v1.Id,
(
select
Col + ','
from
@Values v2
where
v2.Id = v1.Id
order by Col for xml path('')
) as Columns
from
@Values v1
group by
Id
) cols
January 13, 2012 at 9:20 am
This will return an extra comma on the right, but trimming it should be an easier task:
select ID,
(case when var1 = 1 then 'var1,' else '' end +
case when var2 = 1 then 'var2,' else '' end +
case when var5 = 1 then 'var5,' else '' end +
case when var7 = 1 then 'var7,' else '' end +
case when var9 = 1 then 'var9,' else '' end) as NewCol
from myTable
Parenthesis are not necessaries, but I added to clarify the code.
I hope this will be helpful for you.
January 13, 2012 at 5:27 pm
That's the approach I would up taking, thanks to a very elegant solution proposed by someone on another forum.
http://stackoverflow.com/questions/8831059/difficult-t-sql-programming-task
Thanks to all who replied!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply