creating table

HI! I have and existing table with records already and i want to create a table out of it. To illustrate this is how my existing table looks like the first pic and the second pic is what I will be creating out from the first table.


Is this possible? Kindly please show me how? I am using sql2005 express and I have installed management studio express too.

I have did this by creating a temp table.

First i create the temp table and the extra fields required (one for each subject). Then i select the main data, followed by updates for each subject, using ther IDNO, YR and SUBJECT field for matching it up.

I then select the required data.

Code:

---

-- DROP table if it exist
--DROP TABLE #temps

-- Create the temp table
CREATE TABLE #temps
                        (
        [UKEY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [IDNO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [LASTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [FIRSTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [MI] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [GENDER] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [YR] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [SECTIONNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [RDG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [LANG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [MATH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [MUSIC] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [ARTS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [GRADE] [int] NULL
)

-- Insert into the temp table
Insert into #temps
(
                UKEY,
                IDNO,
                LASTNAME,
                FIRSTNAME,
                MI,       
                GENDER,
                YR,
                SECTIONNAME
)
SELECT        UKEY,
                IDNO,
                LASTNAME,
                FIRSTNAME,
                MI,       
                GENDER,
                YR,
                SECTIONNAME
FROM tbl_Test -- **Change this name to match YOUR table name**

-- Update RDG
UPDATE #temps
SET RDG = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'RDG%'

-- Update LANG
UPDATE #temps
SET LANG = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'LANG%'

-- Update MATH
UPDATE #temps
SET MATH = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MATH%'

-- Update MUSIC
UPDATE #temps
SET MUSIC = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MUSIC%'

-- Update ARTS
UPDATE #temps
SET ARTS = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'ARTS%'

-- SELECT formatted list
SELECT          IDNO,
                LASTNAME,
                FIRSTNAME,
                MI,       
                GENDER,
                YR,
                SECTIONNAME,
                RDG,
                LANG,
                MATH,       
                MUSIC,
                ARTS
               
                               
FROM #temps
GROUP BY        IDNO,
                LASTNAME,
                FIRSTNAME,
                MI,       
                GENDER,
                YR,
                SECTIONNAME,
                RDG,
                LANG,
                MATH,
                MUSIC,
                ARTS

---

ps. Hopefully i've got the format and fieldnames right for you to run it.

pps. Test Date and example output below

Code:

---

-- TEST DATA

CREATE TABLE [dbo].[tbl_Test](
        [UKEY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [IDNO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [LASTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [FIRSTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [MI] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [GENDER] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [YR] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [SECTIONNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [SUBJECT] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [GRADE] [int] NULL
) ON [PRIMARY]

INSERT INTO tbl_test
VALUES('30882', 'EL1', 'JONES', 'ALEX', 'D.', 'M', 'LC1', 'Section A', 'RDG1', 95)

INSERT INTO tbl_test
VALUES('30883', 'EL1', 'JONES', 'ALEX', 'D.', 'M', 'LC1', 'Section A', 'LANG1', 97)

INSERT INTO tbl_test
VALUES('30884', 'EL1', 'JONES', 'ALEX', 'D.', 'M', 'LC1', 'Section A', 'MATH1', 97)

INSERT INTO tbl_test
VALUES('30888', 'EL1', 'ZED', 'FRANK', 'A.', 'M', 'LC1', 'Section B', 'RDG1', 90)

INSERT INTO tbl_test
VALUES('30888', 'EL1', 'ZED', 'FRANK', 'A.', 'M', 'LC1', 'Section B', 'LANG1', 78)

INSERT INTO tbl_test
VALUES('30885', 'EL1', 'JONES', 'ALEX', 'D.', 'M', 'LC2', 'Section A', 'RDG2', 95)

INSERT INTO tbl_test
VALUES('30886', 'EL1', 'JONES', 'ALEX', 'D.', 'M', 'LC2', 'Section A', 'LANG2', 97)

INSERT INTO tbl_test
VALUES('30887', 'EL1', 'JONES', 'ALEX', 'D.', 'M', 'LC2', 'Section A', 'MATH2', 97)

---

thanks for the reply. I will try this tonight at home.

Great! it works perfectly. I have some concerns.
1. Why did you use temporary table, not the normal table?
2. How do incorporate this use this code in vb2005?
3. will it be possible that everytime users will save data to tbl_test the other table (temp table/normal table) will be updated too? (user accessed the table through the network (datatable))?
4. will this work too if the tbl_test columns are not all present in the temp/normal table. ( I mean only selected columns in the tbl_test are in the temp/normal table?

Great! it works perfectly. I have some concerns.
1. Why did you use temporary table, not the normal table?

Just in case you where more interested in the output than the extra work i did to arrange the data around. As you want to create a table out if it, i would suggest changing the code to use a normal table. (E.g. Change '#temps' to 'temps')

2. How do incorporate this use this code in vb2005?

I would suggest creating it as a stored procedure and calling that stored procedure via vb2005. (There should be a good few articles in here running Stored Procedures in vb2005)

3. will it be possible that everytime users will save data to tbl_test the other table (temp table/normal table) will be updated too? (user accessed the table through the network (datatable))?

This could be done by setting up an update SQL statement (maybe as a stored procedure) which could be called when users access the table to update the figures. I presume the user accessing it via the network is still through a vb2005 program. You could set it up as an update trigger on the SQL Server Database itself, or run the SQL overnight as a job, using something like SQL Server Agent, or via the command prompt.

4. will this work too if the tbl_test columns are not all present in the temp/normal table. ( I mean only selected columns in the tbl_test are in the temp/normal table?

The tbl_temp table was only created by me help work out how to do the select the way you wanted it. Going forward i would expect that you change the code so that all references pointing to tbl_Test will be change to point at the original table with all the exisiting records in.

Feel free to ask anymore questions

I would suggest creating it as a stored procedure and calling that stored procedure via vb2005. (There should be a good few articles in here running Stored Procedures in vb2005)

I afraid my knowlegde for SPROC is not enough for me to come up a good and working SPROC

This could be done by setting up an update SQL statement (maybe as a stored procedure) which could be called when users access the table to update the figures. I presume the user accessing it via the network is still through a vb2005 program. You could set it up as an update trigger on the SQL Server Database itself, or run the SQL overnight as a job, using something like SQL Server Agent, or via the command prompt.

So you are telling that when the user click a save button, aside for my code to save the back the data to the table is another code to update the new table? I haven't used trigger yet.

The tbl_temp table was only created by me help work out how to do the select the way you wanted it. Going forward i would expect that you change the code so that all references pointing to tbl_Test will be change to point at your main table with all the data in.

I am assuming here that even if the two tables are not exactly alike it will still going to work and yes I will be pointing all the references to my own table.

Can you please help me out with the SPROC?

Thanks again. :thumb: :thumb: :thumb: .

So you are telling that when the user click a save button, aside for my code to save the back the data to the table is another code to update the new table? I haven't used trigger yet.

Yes you could run another piece of SQL to update the new table. The question is though, do you want to keep this new table for other purposes other that providing a way to view the data better?

If the new table is only there to help with displaying the data, then maybe you could just build up the new table from scratch whenever you need to view the data in your new format. That way you dont have to worry about updates.

I am assuming here that even if the two tables are not exactly alike it will still going to work and yes I will be pointing all the references to my own table.

Yes, hopefully i have got all the field names right in my original example so it should be easy to point all references to you own table.

Can you please help me out with the SPROC?

To create the SQL as a stored procedure. (based on original example with the drop table SQL at the start of the stored procedure)

COPY and PASTE this into a new query window and execute it. You should then see a stored procedure called usp_CreateNewTable located in the Programmability > Stored Procedure Folder within your Database.

To run via SQL just type usp_CreateNewTable

Code:

---

CREATE PROCEDURE [dbo].[usp_CreateNewTable] AS

-- DROP table if it exist
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[temps]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[temps]

-- Create the temp table
CREATE TABLE temps
                        (
        [UKEY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [IDNO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [LASTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [FIRSTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [MI] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [GENDER] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [YR] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [SECTIONNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [RDG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [LANG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [MATH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [MUSIC] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [ARTS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [GRADE] [int] NULL
)

-- Insert into the temp table
Insert into temps
(
                UKEY,
                IDNO,
                LASTNAME,
                FIRSTNAME,
                MI,       
                GENDER,
                YR,
                SECTIONNAME
)
SELECT        UKEY,
                IDNO,
                LASTNAME,
                FIRSTNAME,
                MI,       
                GENDER,
                YR,
                SECTIONNAME
FROM tbl_Test -- **Change this name to match YOUR table name**

-- Update RDG
UPDATE temps
SET RDG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'RDG%'

-- Update LANG
UPDATE temps
SET LANG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'LANG%'

-- Update MATH
UPDATE temps
SET MATH = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MATH%'

-- Update MUSIC
UPDATE temps
SET MUSIC = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MUSIC%'

-- Update ARTS
UPDATE temps
SET ARTS = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'ARTS%'

-- SELECT formatted list
SELECT          IDNO,
                LASTNAME,
                FIRSTNAME,
                MI,       
                GENDER,
                YR,
                SECTIONNAME,
                RDG,
                LANG,
                MATH,       
                MUSIC,
                ARTS
               
                               
FROM temps
GROUP BY        IDNO,
                LASTNAME,
                FIRSTNAME,
                MI,       
                GENDER,
                YR,
                SECTIONNAME,
                RDG,
                LANG,
                MATH,
                MUSIC,
                ARTS

---

I haven't tried your post above as i was trying to work on your first post using my own table but i have not succeded yet. I am not able to insert (get) the grade from my source table to the target table. Here is my script for my source table

Code:

---

USE [NDDU-IBED]
GO
/****** Object:  Table [dbo].[tblEnrol1]    Script Date: 07/31/2008 21:50:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEnrol1](
        [Ukey] [bigint] IDENTITY(1,1) NOT NULL,
        [IDNo] [nvarchar](10) NOT NULL,
        [LastName] [nvarchar](50) NULL,
        [FirstName] [nvarchar](50) NULL,
        [MiddleName] [nvarchar](50) NULL,
        [Gender] [nvarchar](6) NULL,
        [Address] [nvarchar](200) NULL,
        [YearLevel] [nvarchar](20) NULL,
        [SubjectName] [nvarchar](20) NULL,
        [Time] [nvarchar](50) NULL,
        [SectionName] [nvarchar](50) NULL,
        [Teacher] [nvarchar](50) NULL,
        [ItemA1_1] [numeric](3, 0) NULL,
        [A1_1] [numeric](3, 0) NULL,
        [ItemA2_1] [numeric](3, 0) NULL,
        [A2_1] [numeric](3, 0) NULL,
        [ItemA3_1] [numeric](3, 0) NULL,
        [A3_1] [numeric](3, 0) NULL,
        [ItemQ1_1] [numeric](3, 0) NULL,
        [Q1_1] [numeric](3, 0) NULL,
        [ItemQ2_1] [numeric](3, 0) NULL,
        [Q2_1] [numeric](3, 0) NULL,
        [ItemQ3_1] [numeric](3, 0) NULL,
        [Q3_1] [numeric](3, 0) NULL,
        [ItemR1_1] [numeric](3, 0) NULL,
        [R1_1] [numeric](3, 0) NULL,
        [ItemR2_1] [numeric](3, 0) NULL,
        [R2_1] [numeric](3, 0) NULL,
        [ItemR3_1] [numeric](3, 0) NULL,
        [R3_1] [numeric](3, 0) NULL,
        [ItemPrj1] [numeric](3, 0) NULL,
        [Prj1] [numeric](3, 0) NULL,
        [ItemSP1] [numeric](3, 0) NULL,
        [SP1] [numeric](3, 0) NULL,
        [ItemRDG1] [numeric](3, 0) NULL,
        [RDG1] [numeric](3, 0) NULL,
        [ItemExam1] [numeric](3, 0) NULL,
        [Exam1] [numeric](3, 0) NULL,
        [TotA1] [numeric](3, 0) NULL,
        [TotA1skor] [numeric](3, 0) NULL,
        [A1Percent] [numeric](3, 0) NULL,
        [A1Result] [numeric](5, 2) NULL,
        [TotQ1] [numeric](3, 0) NULL,
        [TotQ1skor] [numeric](3, 0) NULL,
        [Q1Percent] [numeric](3, 0) NULL,
        [Q1Result] [numeric](5, 2) NULL,
        [TotR1] [numeric](3, 0) NULL,
        [TotR1skor] [decimal](3, 0) NULL,
        [R1Percent] [numeric](3, 0) NULL,
        [R1Result] [numeric](5, 2) NULL,
        [TotPrj1] [numeric](3, 0) NULL,
        [TotPrj1skor] [numeric](3, 0) NULL,
        [Prj1Percent] [numeric](3, 0) NULL,
        [Prj1Result] [numeric](5, 2) NULL,
        [TotSP1] [numeric](3, 0) NULL,
        [TotSP1skor] [numeric](3, 0) NULL,
        [SP1Percent] [numeric](3, 0) NULL,
        [SP1Result] [numeric](5, 2) NULL,
        [TotRDG1] [numeric](3, 0) NULL,
        [ToRDG1skor] [numeric](3, 0) NULL,
        [RDG1Percent] [numeric](3, 0) NULL,
        [RDG1Result] [numeric](5, 2) NULL,
        [TotExam1] [numeric](3, 0) NULL,
        [TotExam1skor] [numeric](3, 0) NULL,
        [Exam1Percent] [numeric](3, 0) NULL,
        [Exam1Result] [numeric](5, 2) NULL,
        [TG1] [numeric](5, 2) NULL,
        [FG1] [numeric](5, 0) NULL,
        [Rem1] [nvarchar](10) NULL

 CONSTRAINT [PK_tblEnrol1] PRIMARY KEY CLUSTERED
(
        [Ukey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

---

and for the temp table here is my script

Code:

---

-- Create the temp table
CREATE TABLE TEMP
                        (
        [UKEY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [IDNO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [LASTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [FIRSTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [MIDDLENAME] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [GENDER] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [YEARLEVEL] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [SECTIONNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [ENGLISH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [MATH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,

)

-- Insert into the temp table
Insert into TEMP
(
                UKEY,
                IDNO,
                LASTNAME,
                FIRSTNAME,
                MIDDLENAME,       
                GENDER,
                YEARLEVEL,
                SECTIONNAME
)
SELECT        UKEY,
                IDNO,
                LASTNAME,
                FIRSTNAME,
                MIDDLENAME,       
                GENDER,
                YEARLEVEL,
                SECTIONNAME
FROM tblENROL1 -- **Change this name to match YOUR table name**

-- Update RDG
UPDATE TEMP
SET ENGLISH = t2.FG1
FROM tblENROL1 t2
WHERE temp.IDNO = t2.IDNO
AND temp.YEARLEVEL = t2.YEARLEVEL
AND t2.SUBJECTNAME LIKE 'ENGLISH%'

-- Update MATH
UPDATE TEMP
SET MATH = t2.FG1
FROM tblENROL1 t2
WHERE TEMP.IDNO = t2.IDNO
AND TEMP.YEARLEVEL = t2.YEARLEVEL
AND t2.SUBJECTNAME LIKE 'MATH%'

---

as you can see in the script I am only interested in getting the FG1 column but i am not able to get it right.

By the way the I shortened the script of my source table. there is also FG2, FG3, FG4, Final FG which I will be needing later on.

Run this code below and show me what it brings back.

Code:

---

SELECT TOP 20        UKEY,
                IDNO,
                LASTNAME,
                FIRSTNAME,
                MIDDLENAME,       
                GENDER,
                YEARLEVEL,
                SECTIONNAME,
                SUBJECTNAME,
                FG1,
               
FROM tblENROL1

---

Also It the Select part of your statement populating ok.

e.g.

Code:

---

SELECT        UKEY,
                IDNO,
                LASTNAME,
                FIRSTNAME,
                MIDDLENAME,       
                GENDER,
                YEARLEVEL,
                SECTIONNAME
FROM tblENROL1 -- **Change this name to match YOUR table name**

---

the first code, i get 20 records-- (20 row(s) affected). and yes the select part of the statement is populating correctly this is what I get (30339 row(s) affected).

In my code, only the FG1 column has no value

So i presume the two updates in your example return 0 rows?

If so turn those updates in SELECT statements for testing purposes to see if it helps you to understand whats going wrong.

eg. You could try the select 3 times with different parts of the statement commented out.

Code:

---

-- Select for English (on ID join only)
SELECT t2.FG1, t2.IDNO, t2.YEARLEVEL, t2.SUBJECTNAME, *
FROM tblENROL1 t2
WHERE temp.IDNO = t2.IDNO
--AND temp.YEARLEVEL = t2.YEARLEVEL
--AND t2.SUBJECTNAME LIKE 'ENGLISH%'

---

Code:

---

-- Select for English (ID and YEARLEVEL)
SELECT t2.FG1, t2.IDNO, t2.YEARLEVEL, t2.SUBJECTNAME, *
FROM tblENROL1 t2
WHERE temp.IDNO = t2.IDNO
AND temp.YEARLEVEL = t2.YEARLEVEL
--AND t2.SUBJECTNAME LIKE 'ENGLISH%'

---

Code:

---

-- This one should bring back the amount of rows which will get updated to the TEMP table in the UPDATE statement
SELECT t2.FG1, t2.IDNO, t2.YEARLEVEL, t2.SUBJECTNAME, *
FROM tblENROL1 t2
WHERE temp.IDNO = t2.IDNO
AND temp.YEARLEVEL = t2.YEARLEVEL
AND t2.SUBJECTNAME LIKE 'ENGLISH%'

---

Are any of those three examples returning rows?

ps. Make sure there is data in the temp table (based on the initial INSERT above the update statements)

Why are you creating a table that contains the same data as another table??

Why not just use a VIEW of the existing table and use GROUP BY with CASE/WHEN statements to accomplish the column organization?

How many columns can this get to be - I see RDG1 and RDG2 - is there also a RDG3?

If the amount of columns is somewhat variable then this shouldn't be in your database but should instead be done client side.

I need to get the grade of each of the subjects of the student and put it in my report so that I can have a produce a report card that will show all the grade of the student. Because with my current table (the first picture in my first post) I am not able to display the grade of a student for all the subjects he/she is enrolled in.

I asked two more questions that are really important for my consideration of a method to use. Please re-read my post and answer all questions.

Quote:

---

Originally Posted by szlamany

Why are you creating a table that contains the same data as another table??

Why not just use a VIEW of the existing table and use GROUP BY with CASE/WHEN statements to accomplish the column organization?

How many columns can this get to be - I see RDG1 and RDG2 - is there also a RDG3?

If the amount of columns is somewhat variable then this shouldn't be in your database but should instead be done client side.

---

first of all I dont know anything about VIEW and second of all, I have rdg1 up to rdg6 and there are other subjects i have in my existing table to wit: English1 to english4, Math1 to Math4, Science1 to Science4 and many others.

As I mentioned in my last post I will be using the table to generate a report card.

I would like this part of the code to be SPROC and then use it vb.net 2005 on click of a button. How is that possible?

Code:

---

-- Update RDG
UPDATE temps
SET RDG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'RDG%'

-- Update LANG
UPDATE temps
SET LANG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'LANG%'

-- Update MATH
UPDATE temps
SET MATH = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MATH%'

-- Update MUSIC
UPDATE temps
SET MUSIC = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MUSIC%'

-- Update ARTS
UPDATE temps
SET ARTS = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'ARTS%'

---

As a stored procedure

Code:

---

CREATE PROCEDURE usp_Update_Subjects
AS

-- Update RDG
UPDATE temps
SET RDG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'RDG%'

-- Update LANG
UPDATE temps
SET LANG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'LANG%'

-- Update MATH
UPDATE temps
SET MATH = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MATH%'

-- Update MUSIC
UPDATE temps
SET MUSIC = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MUSIC%'

-- Update ARTS
UPDATE temps
SET ARTS = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'ARTS%'

---

and an example of how you could create the VB code to call it.
(Place code below in button click event)

vb Code:

' Create the connection
            Dim lconn As New SqlConnection("Connection in here")
 
            ' Create the command
            Dim lcommand As New SqlCommand
 
            '  Create the datareader
            Dim lsqlDR As SqlDataReader
 
            Try
 
                ' If the connection is closed
                If lconn.State = ConnectionState.Closed Then
 
                    ' Open the connection
                    lconn.Open()
 
                End If
 
                ' Set the command text to the name of the stored procedure
                lcommand.CommandText = "usp_Update_Subjects"
 
                ' Set the command text to be a stored procedure
                lcommand.CommandType = CommandType.StoredProcedure
 
                ' Execute the reader
                lsqlDR = lcommand.ExecuteReader()
 
            Catch ex As Exception ' Catch the error
 
                ' Do some error handling in here
 
            End Try

It is possible to take each of those UPDATE queries and instead make them sub-queries in a SELECT list. Each sub-query would basically be the exact same syntax as the UPDATE shows.

You should use SELECT TOP 1 in the sub-query in case a student has both RDG1 and RDG2.

No real need to build a temp table - although that certainly can be done.

Thanks to both of you! I will try it.