|
Merci Ashfield.
I used your solution :
SELECT ID_STUDENT, CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,<br />
CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar,<br />
[122]) END AS note3,<br />
CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3<br />
<br />
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE, CODE<br />
FROM EXAMEN) p PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt<br />
<br />
<br />
I have not the problem of data type but I have the data displayed by double
Here are my data TABLE examen
Id_student---- id_feild ---- note----code
9306-------------121 ----------- 0-------1
9306-------------122---- -------4-------0
9306------------123----------- 12----- 0
9307------------121 ------------3------ 0
9307------------122------------ 0------ 1
9307------------123 ------------9------ 0
9308------------121------------ 12------0
9308------------122------------ 4------ 0
9308------------123------------ 9------0
following the requet résultatt
9306----NULL----NULL----NULL
9307----NULL----NULL----NULL
9308----NULL----NULL-----NULL
9306----NULL----4-------12
9307-----3------NULL----9
9308-----12-----4------9
9306-----ASD----ASD-----ASD
9307-----ASD----ASD-----ASD
and this is what I want
id_student---note1----note2---note3
9306---------ASD-------4---------12
9307---------3-------ASD -------9
9308--------12------4---- ------9
I dont know why please help me.
Thanks
modified on Tuesday, February 17, 2009 6:32 AM
|
|
|
|
|
Hi!
I think I have this result because we must specify
that the ID_FIELD= 121 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
and ID_FIELD=122 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [122]) END AS note2
how ? the case when accepts two conditions?
thanks!
|
|
|
|
|
I don't if I follow the logic but if you drop away the code in pivot like this:
SELECT *
FROM (SELECT ID_FIELD,
ID_STUDENT,
NOTE
FROM EXAMEN) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt
you will get a result as wanted where note is 0 for the rows where code is 1 (is this always true?):
ID_STUDENT 121 122 123
---------- --- --- ---
9306 0 4 12
9307 3 0 9
9308 12 4 9
Now if you replace the zeros like:
SELECT ID_STUDENT,
CASE WHEN ([121]) = 0 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
CASE WHEN ([122]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [122]) END AS note2,
CASE WHEN ([123]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD,
ID_STUDENT,
NOTE
FROM EXAMEN) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt
you will get:
ID_STUDENT note1 note2 note3
---------- ----- ----- -----
9306 abs 4 12
9307 3 ASD 9
9308 12 4 9
Is this getting you any closer to the result?
|
|
|
|
|
Thank you very much Mika Wendelius you helped me a lot in this site  .
for me code = 1 means that the student is abcent so his note= 0 and I have to replace 0 by 'abc' to differentiate between a student absent and another who is present and received 0.why I used code code_absence.
I do not know if I come to explain my problem.
Thnaks.
|
|
|
|
|
It didn't come out very pretty but could you use this for somekind of starting point. The idea is that the first query gets the records where the student is absent and the second gets those where the student isn't absent. then these are joined and columns are combined:
SELECT coalesce(a.ID_STUDENT, b.ID_STUDENT) as id_student,
coalesce(a.note1, b.note1) as note1,
coalesce(a.note2, b.note2) as note2,
coalesce(a.note3, b.note3) as note3
FROM
(SELECT ID_STUDENT,
CASE WHEN ([121]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [121]) END AS note1,
CASE WHEN ([122]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [122]) END AS note2,
CASE WHEN ([123]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 1) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) a
full outer join
(SELECT ID_STUDENT,
CONVERT(varchar, [121]) AS note1,
CONVERT(varchar, [122]) AS note2,
CONVERT(varchar, [123]) AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 0) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) b
on a.ID_STUDENT = b.ID_STUDENT
The result is:
id_student note1 note2 note3
---------- ----- ----- -----
9306 Abs 4 12
9307 3 Abs 9
9308 12 4 9
|
|
|
|
|
Hi.
Thank you a lot ,merci beaucoup Mika Wendelius it works .
|
|
|
|
|
You're welcome
|
|
|
|
|
Not sure if this is the location where I should post such a question (if not please direct me to the proper forum), but I am designing a report in VS2005 and need to have a date field display blank or an empty string if the date is 1/1/1900. I have written the IIf statement as such:
=IIF(Fields!seStartDate.Value = '1/1/1900', '', Format(Fields!seStartDate.Value, "MM/d/yyyy")
, however I am getting a syntax error. Please advise as to how this should be written...thanks in advance to all that reply.
|
|
|
|
|
Just some hints as I don't have the chance to test it. Try the following:
=IIF(Format(Fields!seStartDate.Value, "MM/d/yyyy") = '1/1/1900', '', Format(Fields!seStartDate.Value, "MM/d/yyyy")
May be you want to try this as well:
=IIF(Fields!seStartDate.Value.ToString() = '1/1/1900', '', Format(Fields!seStartDate.Value, "MM/d/yyyy")
I am not sure whether you should us the double quotes (") within the format function or the single quotes for = '1/1/1900'.
Good luck,
Florian
|
|
|
|
|
Suppose we have a table like this:
CREATE TABLE Slots (
SlotNumber INT NOT NULL,
...,
PRIMARY KEY(SlotNumber)
);In this table, a maximum of X slots can be inserted. Given a table with Y rows in it, what is the next available slot?
One solution is to keep a second table with all the slots in it:
CREATE TABLE AllSlots (
SlotNumber INT NOT NULL,
PRIMARY KEY(SlotNumber)
);Then I could do something like
SELECT SlotNumber
FROM AllSlots
WHERE SlotNumber NOT IN (SELECT SlotNumber FROM Slots)
Does anybody have a better solution? Note that I have only ANSI SQL in my arsenal (no fancy TSQL/PL1/Whatever constructs are at my disposal)
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
Just wondering, why don't you simply take MAX(SlotNumber) + 1 (if you used that, I wouldn't recommend to use the SlotNumber as primary key, but as a unique key and define a different primary key).
If the MAX + 1 is less than maximum amount of slots, you still have a free slot.
If the slots are not filled in order, I think your solution is fine. Just when querying, you should take top 1 row if you need only one empty slot.
modified on Monday, February 16, 2009 1:27 PM
|
|
|
|
|
I forgot to mention that there's an upper and lower limit of the slots. The limits may be 1 and 999 for example. Essentially, the query would be "pick any number between 1 and 999, that isn't found in the table".
For small ranges such as [1, 999], it's probably easiest to select everything and order by the primary key (a no op, since the primary key index is sorted already), and then scan for the next pair of adjacent slots X and Y where X + 1 != Y. That means that X + 1 must be available. This is however not feasible if the slot span is large-ish, because it'll degrade to a table scan... 
|
|
|
|
|
Don't know if you meant this but if you need single slots, could you use the same table and find rows that have gaps between a row and next row in order. Something like
select ...
from slots s1
where not exists (select 1
from slots s2
where s2.slotnumber = s1.slotnumber + 1)
This wouldn't lead to table scan since it's fully an index operation. However the first slot cannot be found using this so you would have to find it differently
|
|
|
|
|
I (well - my colleague to be honest) found a similar solution:
SELECT Slot + 1 AS NextSlot
FROM Slots
WHERE NextSlot NOT IN (SELECT Slot FROM Slots) Here I'll have to distinguish between no slots and all slots unavailable, because these two edge cases will produce an empty result set.
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
That would work also. However there's a slight difference between the variations. I believe your query will perform fine if the dbms is capable of doing and predicting index joins. If it's not, it can lead to table scan to create the intermediate result set from slots.
On the other hand the exists structure may perform well if the execution can be stopped immediately when the first row is found meaning that not exists will never be true (most of dbms's can do this).
So what I'm saying is that I think you should test both variations and get the execution plan to see which one (or perhaps some derived solution) will lead to best result.
|
|
|
|
|
This version also works with an empty table, but it still fails if you remove the first slot.
SELECT Coalesce(Min(s1.Slotnumber) + 1, 1)
FROM slots s1 LEFT JOIN slots s2
ON s1.Slotnumber = s2.Slotnumber - 1
WHERE s2.Slotnumber IS NULL
AND s1.Slotnumber<999
|
|
|
|
|
I forgot the most important thing: thank you for your effort! Much appreciated!
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
You're welcome
|
|
|
|
|
CREATE TABLE slot(
id int not null,
slot1 bit,
slot2 bit,
slot... bit,
slotn bit,
...
)
J/k. I would use the associate table approach that contains the list of available slots exactly as you have mentioned. Its not fancy, but it is efficient and easy to read.
Need custom software developed? I do C# development and consulting all over the United States.
If you don't ask questions the answers won't stand in your way.
Doing a job is like selecting a mule, you can't choose just the front half xor the back half so when you ask me to do a job don't expect me to do it half-assed.
|
|
|
|
|
How about having a "Reserved" bit to set or clear as the slot is used and freed up?
A third-party product my former employer used did that, there was a table that always contained 8000 rows, and the system used them to hold values until it needed them, then freed them afterward.
|
|
|
|
|
I thought about that, but the situation is a bit more complicated than it seems. I have a bunch of satellites (clients) and a hub (server). The clients can hold up to 10k rows in each table. These tables will later be merged on the server, meaning that if there are N satellites, there may be up to N * 10k rows on the server. Using a status bit, there will be exactly 10k rows on the satellites, and N * 10k rows on the server. N is arbitrary. It could be 1 or it could be 50. I want to keep the system as fast as possible, because the server should be able to run on a fairly small sized machine.
I also prefer to not having hard coded values in a database, because frankly, you never know what can happen to the hard coded values. 
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
I have a table, which has two rows
1.EmailID
2.EmailAddress
I have Duplicate Email Addresses and i want to Delete Duplicate Email Addresses from my table except the max(emailId)
and I have millions of Duplicate EmailAddresses in my table
Plz ans me as soon as possible....
Thanks!
|
|
|
|
|
Don't double post. It's considered rude.
|
|
|
|
|
I have been Double posted in last two days but I do not recieve a single ans to help out from this satuation..........yyyyyyyyyyyyy
|
|
|
|
|
You mean you have been waiting 2 days when it takes 5 secondes to do a search. There are pleanty of answers here clickety[^]
Learn to use the tools you have available instead of whining that no one is helping you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
