|
I have mysql table which stores students marks. The first column represent the unique Regno For each student, the second reprsents TotalScore and the rest of the rows represent units name. Note...The columns representing different can be increased if a new unit is added. Therefore i wish to know how i can loop through the records of each student ,add them and insert into TotalScore column...am still new to vb.net. Kindly assist
the table is as follows:
REGNO TOTALSCORE UNIT1,unit2,unit3,unit4,...UNITn
1
2
3
.
.
n
-- modified 17-Feb-15 23:49pm.
|
|
|
|
|
KipkoechE wrote: i wish to know how i can loop through the records of each student and insert into TotalScore column
What do you want to insert into the TotalScore column?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
The sum of each row as identified by the regno
|
|
|
|
|
How about executing the query:
UPDATE tablename SET TotalScore = (unit1 + unit2 + unit3 + unit4) // and so on?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
AM trying to avoid that method because the number of columns can be changed at runtime
|
|
|
|
|
KipkoechE wrote: the number of columns can be changed at runtime
How is that possible? Are you creating a new table with a different number of columns and then deleting the first table?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
By altering the table when a new unit is added
|
|
|
|
|
Column1 Column2
-------- ----------
Vaugh William
Vaugh Smith
Woods Jane
Expected Output :
Vaugh, William-Smith/Woods,Jane
Condition : There could be n number of rows in the sample table, there could be n number of woods too.
display all the common surname having different names meaning repeated Last names will be once then all the first names associated with it.
truncated at 30 characters max.
How can I get this output in SQL query ?
|
|
|
|
|
This is not clear.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
What extra information will make it clear ?
|
|
|
|
|
It looks like you want a single record as your output? What are the conditions?
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
yes there is just single cell output.
but conditions are that cell can not have more than 30 characters.
Even there are 10 or 15 rows in a table, the SQL query should give single cell output.
|
|
|
|
|
That doesn't make any sense. You want to concatenate everything into a single value? If so you can do similar to:
DECLARE @temp NVARCHAR(MAX)
SELECT @temp = COALESCE(@temp + ', ', '') + field1
FROM table
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I want to display the unique last names followed by their respective first names separated by a '-' and, then '/' , then again the next last name(If it is not distinct , then add their first names separated by '-')followed by its respective firstname and so on..
|
|
|
|
|
How do you know which first name belongs to which last name?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
This sort of manipulation is best done in the UI, not the database.
However, using the techniques from this blog post[^] leads to the following (rather ugly) solution:
SELECT
STUFF(
(SELECT '/' + Surname + ',' + STUFF(
(SELECT '-' + Forename
FROM YourTable As T2
WHERE T2.Surname = T1.Surname
ORDER BY T2.Forename
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM YourTable As T1
GROUP BY Surname
ORDER BY Surname
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As CombinedNames
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am getting the combined names for the whole table. How to restrict it for a single id? Can you please tell where should I put the condition in the code you have shown above ?
|
|
|
|
|
What do you mean by "a single id"? There's no mention of any column called "id" in your question, or any of the other messages in this thread.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
ID Column1 Column2
-- -------- ----------
7 Vaugh William
7 Vaugh Smith
6 Woods Jane
6 Woods Joseph
6 Wright Adam
6 Wright John
Suppose I have to find the combined name only for id=6 , then where should I put the condition in the code?
The expected output :
Woods,Jane-Joseph/Wright,Adam-John
|
|
|
|
|
So the ID is the same for each surname?
In that case, you just need to add a WHERE clause between the FROM YourTable As T1 and GROUP BY Surname lines:
SELECT
STUFF(
(SELECT '/' + Surname + ',' + STUFF(
(SELECT '-' + Forename
FROM YourTable As T2
WHERE T2.Surname = T1.Surname
ORDER BY T2.Forename
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM YourTable As T1
WHERE T1.ID = @TheIDToFind
GROUP BY Surname
ORDER BY Surname
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As CombinedNames
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
ID Column1 Column2
-- -------- ----------
7 Vaugh William
7 Vaugh Smith
6 Woods Jane
6 Woods Joseph
6 Wright Adam
6 Wright John
Thanks, I exactly put the condition at the place you suggeted, but still the output I get is as below
WRONG OP : Vaugh,William-Smith/Woods,Jane-Joseph/Wright, Adam-John
Expected OP : Woods,Jane-Joseph/Wright, Adam-John
|
|
|
|
|
I've just tried the code here on your sample data, and I get the expected output.
DECLARE @T TABLE
(
ID int NOT NULL,
Surname varchar(10) NOT NULL,
Forename varchar(10) NOT NULL
);
INSERT INTO @T (ID, Surname, Forename)
VALUES
(7, 'Vaugh', 'William'),
(7, 'Vaugh', 'Smith'),
(6, 'Woods', 'Jane'),
(6, 'Woods', 'Joseph'),
(6, 'Wright', 'Adam'),
(6, 'Wright', 'John')
;
SELECT
STUFF(
(SELECT '/' + Surname + ',' + STUFF(
(SELECT '-' + Forename
FROM @T As T2
WHERE T2.Surname = T1.Surname
ORDER BY T2.Forename
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM @T As T1
WHERE T1.ID = 6
GROUP BY Surname
ORDER BY Surname
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As CombinedNames
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Here's a SQL Fiddle with the same query, which also generates the correct output:
http://sqlfiddle.com/#!3/300af/1[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I had to add few extra condition suitable to my table, but it DID WORK !! 
I have learnt about STUFF and FOR XML PATH. Thanks a ton for all your time Richard.
|
|
|
|
|
If my one of customer saying they could a generate a report withing 30 second before and now they have to wait for 3min so how I can track that as a Senior Technical Support Analyst that whether it is a database issue or programming issue ?
Chaminda Bandara
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
