September 14, 2012 at 12:03 pm
Hi,
I have a problem in sql server 2005 to submit :
My table 1 is as follows : 1 column with refid and one with a year (d_an)
Table 1
REFIDd_an
11998
22001
31999
42012
I would like to create new columns for each year from 1998 to 2012;
When value of d_an=year of the variable name then value of this var=1 and all subsequent years too; else, if year before d_an then 0;
Ex: if d_an=2001 then values for d_2001 to d_2012=1 and values for d_1998 to d_2000=0
REFIDd_anD_1998D_1999 …….….D_2001…..D_2012
119981111
220010011
319990111
420120001
Could someone help me on that?
Thanks you in advance
Martine
September 14, 2012 at 12:59 pm
martinetymic (9/14/2012)
Hi,I have a problem in sql server 2005 to submit :
My table 1 is as follows : 1 column with refid and one with a year (d_an)
Table 1
REFIDd_an
11998
22001
31999
42012
I would like to create new columns for each year from 1998 to 2012;
When value of d_an=year of the variable name then value of this var=1 and all subsequent years too; else, if year before d_an then 0;
Ex: if d_an=2001 then values for d_2001 to d_2012=1 and values for d_1998 to d_2000=0
REFIDd_anD_1998D_1999 …….….D_2001…..D_2012
119981111
220010011
319990111
420120001
Could someone help me on that?
Thanks you in advance
Martine
That seems like an incredibly bad idea. What happens next year? Do you need to add another column? What about in 15 years, do you need another column for every year? This goes against the power of relational data. You should instead create another table that contains the year, a foreign key to your base table and the value you want to store.
Create table SomeTableWithYearThatHoldsSomething
(
SomeTableID int,
REFID int,
SomeValue [whatever datatype you need]
Year int
)
You would need to add the primary key and foreign key constraints but you get the idea.
If you are deadset on having your data be not normalized you can simply add them.
alter table SomeNamelessTable
add d_1998 bit
Repeat that endlessly until you have denormalized your data to the point that you are satisfied.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2012 at 1:12 pm
You could do something like:
SELECT RefID,
d_an,
CASE WHEN d_an >= 1998 THEN 1 ELSE 0 END AS d_1998,
CASE WHEN d_an >= 1999 THEN 1 ELSE 0 END AS d_1999,
CASE WHEN d_an >= 2000 THEN 1 ELSE 0 END AS d_2000,
CASE WHEN d_an >= 2001 THEN 1 ELSE 0 END AS d_2001,
CASE WHEN d_an >= 2002 THEN 1 ELSE 0 END AS d_2002,
CASE WHEN d_an >= 2003 THEN 1 ELSE 0 END AS d_2003,
CASE WHEN d_an >= 2004 THEN 1 ELSE 0 END AS d_2004,
CASE WHEN d_an >= 2005 THEN 1 ELSE 0 END AS d_2005,
CASE WHEN d_an >= 2006 THEN 1 ELSE 0 END AS d_2006,
CASE WHEN d_an >= 2007 THEN 1 ELSE 0 END AS d_2007,
CASE WHEN d_an >= 2008 THEN 1 ELSE 0 END AS d_2008,
CASE WHEN d_an >= 2009 THEN 1 ELSE 0 END AS d_2009,
CASE WHEN d_an >= 2010 THEN 1 ELSE 0 END AS d_2010,
CASE WHEN d_an >= 2011 THEN 1 ELSE 0 END AS d_2011,
CASE WHEN d_an >= 2012 THEN 1 ELSE 0 END AS d_2012
Or you could simply do it the right way and stop doing nonsense with your data.Why do you need this? This table won't really help you for anything.
September 14, 2012 at 1:15 pm
Oh wow...I just looked a little closer at the details. You need to look at the numbers or tally table. This is not the right way to do whatever it is you are trying to do.
http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2012 at 1:44 pm
martinetymic (9/14/2012)
Hi,I have a problem in sql server 2005 to submit :
My table 1 is as follows : 1 column with refid and one with a year (d_an)
Table 1
REFIDd_an
11998
22001
31999
42012
I would like to create new columns for each year from 1998 to 2012;
When value of d_an=year of the variable name then value of this var=1 and all subsequent years too; else, if year before d_an then 0;
Ex: if d_an=2001 then values for d_2001 to d_2012=1 and values for d_1998 to d_2000=0
REFIDd_anD_1998D_1999 …….….D_2001…..D_2012
119981111
220010011
319990111
420120001
Could someone help me on that?
Thanks you in advance
Martine
Would you please explain what it is you are trying to accomplish? I feel like you are only giving us a small part of the actual problem you are trying to solve.
September 14, 2012 at 1:45 pm
Hello,
in fact I did something similar:-)
'd_1998'=
case when diab_d1=1998 then 1
else 0
end,
but it is with the next step that I have some pb:
DECLARE @sql VARCHAR(500)
DECLARE @Annee INT
SET @Annee=1998
WHILE @Annee<=2011
BEGIN
SET @sql='UPDATE diab_an_actif2 SET AN_'+CAST(@Annee+1 AS CHAR(4))+'=1WHERE AN_'+CAST(@Annee AS CHAR(4))+'=1'
PRINT @sql
EXEC (@SQL)
SET @Annee=@Annee+1
END
WhEn I print it works, see below:
UPDATE diab_an_actif2 SET AN_1999=1WHERE AN_1998=1
UPDATE diab_an_actif2 SET AN_2000=1WHERE AN_1999=1
UPDATE diab_an_actif2 SET AN_2001=1WHERE AN_2000=1
but when I execute:
UPDATE diab_an_actif2 SET AN_1999=1WHERE AN_1998=1
Msg 207, Niveau 16, État 1, Ligne 1
Nom de colonne non valide : 'AN_1998'.
UPDATE diab_an_actif2 SET AN_2000=1WHERE AN_1999=1
Msg 207, Niveau 16, État 1, Ligne 1
Nom de colonne non valide : 'AN_1999'.
IS IT BECAUSE I USE A VIEW AND NOT A TABLE?
tHANK YOU FOR YOUR HELP AND YES THIS PROGRAM MEANS SMTHING
September 14, 2012 at 1:50 pm
martinetymic (9/14/2012)
Hello,in fact I did something similar:-)
'd_1998'=
case when diab_d1=1998 then 1
else 0
end,
but it is with the next step that I have some pb:
DECLARE @sql VARCHAR(500)
DECLARE @Annee INT
SET @Annee=1998
WHILE @Annee<=2011
BEGIN
SET @sql='UPDATE diab_an_actif2 SET AN_'+CAST(@Annee+1 AS CHAR(4))+'=1WHERE AN_'+CAST(@Annee AS CHAR(4))+'=1'
PRINT @sql
EXEC (@SQL)
SET @Annee=@Annee+1
END
WhEn I print it works, see below:
UPDATE diab_an_actif2 SET AN_1999=1WHERE AN_1998=1
UPDATE diab_an_actif2 SET AN_2000=1WHERE AN_1999=1
UPDATE diab_an_actif2 SET AN_2001=1WHERE AN_2000=1
but when I execute:
UPDATE diab_an_actif2 SET AN_1999=1WHERE AN_1998=1
Msg 207, Niveau 16, État 1, Ligne 1
Nom de colonne non valide : 'AN_1998'.
UPDATE diab_an_actif2 SET AN_2000=1WHERE AN_1999=1
Msg 207, Niveau 16, État 1, Ligne 1
Nom de colonne non valide : 'AN_1999'.
IS IT BECAUSE I USE A VIEW AND NOT A TABLE?
tHANK YOU FOR YOUR HELP AND YES THIS PROGRAM MEANS SMTHING
I assumed it meant something. I only used those names because we had no details to work with. I STRONGLY urge you to look at normalization. What you have here is the beginning of a nightmare. You structures are going be nothing a pain to deal with as long as you insist on this type of thing.
We can't see from here what you see so it is impossible to tell why you are getting the error you are getting. We don't even know what columns are in the table.
I can however say that a while loop for this is not a good approach. There is no reason to do this RBAR (row by agonizing row).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2012 at 1:51 pm
That means that you don't have the columns in your table/view.
As Lynn said, you can do things in a better way.
If you need help translating, I could help you.
September 14, 2012 at 1:53 pm
We still have a problem here. You haven't given us enough to actually answer your question. We just can't see from here what you see there. Step back a momen, forget everything you know about the problem you are working and look at what you posted. Based soley on what you have posted, is this something you could solve, or are there things missing; like DDL (CREATE TABLE) statements, sample data (INSERT INTO statements), expected results, and explainatioin of how data is processed to give the expected results.
September 14, 2012 at 1:56 pm
and sorry for the format : it's my first time asking questions in a forum!:-)
some details:
d_an = first year with a specific medical condition
before this date : d_YEAR=0
after this date: d_YEAR=1
September 14, 2012 at 2:00 pm
martinetymic (9/14/2012)
and sorry for the format : it's my first time asking questions in a forum!:-)some details:
d_an = first year with a specific medical condition
before this date : d_YEAR=0
after this date: d_YEAR=1
Then may I suggest that you read the first article I reference below in my signature block. Follow the instructions on what to post and how to post it for the best possible answers. Just don't forget the expected results.
September 14, 2012 at 2:01 pm
martinetymic (9/14/2012)
and sorry for the format : it's my first time asking questions in a forum!:-)some details:
d_an = first year with a specific medical condition
before this date : d_YEAR=0
after this date: d_YEAR=1
No problem. It is not the most intuitive thing in the world. 🙂
We really can't do much with what you have posted. Please see the first link in my signature about best practices when posting questions.
--EDIT--
LOL Lynn beat me to the punch again. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply