replacing colume names in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

replacing colume names in a table

Author

Topic

esloat
Starting Member

8 Posts

Posted - 2010-02-24 : 17:19:02

This is my first post. I am fairly new, but not completely inexperienced, with t-sql and I would much appreciate advise on the following:

I have two tables. The first (StudentTable) contains a list of students with responses to test questions. The colume names are:

StudenID, Item1, Item2,...Item10

Each row is the student ID and how the student responded to each item on the test (a '1' or '0' for Item1, Item 2, ...). So it looks like:

StudenID Item1 Item2 ... Item10
101 1 0 1
102 1 1 1
103 0 1 1
...

The second table (ItemDescription) contains the descriptions of each item (colume) header in the StudentTable. It is structured as:

ItemID, ItemDescription

The rows are the item names (Item1, Item2,...), which is the same as the colume headers in the StudentTable, and the item's description (such as "Adding Two Numbers" or "Subtracting Two Numbers"). The table looks like:

ItemID ItemDescription
Item1 Add Two Numbers
Item2 Subtract Two Numbers
Item3 Multiply Two Numbers
...

Without manually typing in a colume alias each time I work with the data, I would like to generate a result set (a Select statement) that substitutes the ItemDescriptions from the ItemDescriptions table for the colume names in the StudentTable.

Finally, the solution I need has to be applied to dozens of these types of testing tables. It just isn't practical for me to create a result set and then manually type in the new colume headers so that I can use the info in SSRS or in other generated reports. I need a generalized solution on how to replace colume names from information contained in an external table.

Any advice of where to look, learn, or examples would be wonderful. I am thinking this is not a unique problem and more experienced SQL experts have seen this category of problem before.

Thanks in advance for any insights.

Ed Sloat

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 01:27:05

It would be much easier if the Item1 ... Item10 were individual records in a sub-table of StudentTable (what is referred to as First Normal Form). Then you could just JOIN StudentTable, StudentItems and ItemDescription

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:06:38

whats the version of sql you're using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esloat
Starting Member

8 Posts

Posted - 2010-02-25 : 14:46:09

Kristen & visakh16:

Thanks for the reply. I will re-connect myself with First Normal Form and try redesigning the table elements.

If I am interpreting your suggestion about restructuring the tables correctly, are you saying that the StudentsTable should be as:

StudentID ItemID Score
111 Item1 0
111 Item2 1
111 Item3 1
222 Item1 0
222 Item2 1
333 Item1 2
333 Item2 0

And then the ItemDescription Table as:

ItemID ItemDescription
Item1 Add
Item2 Subtract
Item3 Multiply

and then join the tables based on ItemID? I greatly appreciate your input, and your patience for someone clearly in the learning mode.

The version of SQL I am using is 2008.

Ed Sloat

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 15:04:58

I think "StudentsTable" is the wrong name for what you now have, but the concept is right.

I think "StudentsTable" name sounds like it should hold "Student ID, Name, Date of birth ..."

and the data you have is "StudentItems"

But hey! Its just a name, so my point is just a suggestion.


DECLARE @StudentsTable TABLE
(
	StudentID int,
	ItemID	varchar(20),
	Score	int
)

INSERT INTO @StudentsTable
SELECT 111, 'Item1', 0 UNION ALL
SELECT 111, 'Item2', 1 UNION ALL
SELECT 111, 'Item3', 1 UNION ALL
SELECT 222, 'Item1', 0 UNION ALL
SELECT 222, 'Item2', 1 UNION ALL
SELECT 333, 'Item1', 2 UNION ALL
SELECT 333, 'Item2', 0

DECLARE @ItemDescription TABLE
(
	ItemID 		varchar(20),
	ItemDescription varchar(20)
)

INSERT INTO @ItemDescription
SELECT 'Item1', 'Add' UNION ALL
SELECT 'Item2', 'Subtract' UNION ALL
SELECT 'Item3', 'Multiply'

SELECT	'S -->', S.*, 'I -->', I.*
FROM	@StudentsTable AS S
	LEFT OUTER JOIN @ItemDescription AS I
		ON I.ItemID = S.ItemID

esloat
Starting Member

8 Posts

Posted - 2010-02-25 : 15:18:15

Kristen,

This is awesome. I did a small example of the table structures I thought you were suggesting (using a left outer join from my studentTable to my ItemDescriptionTable) and it worked great. I think I have the concept for this solution, thanks to you. Now I will work through the code you provided above and learn more. All of my testing tables (about 50) in my datawarehouse are structured as one-row per student with their item responses going across and the studenid as the primary key. My task (I think) will be to create a stored procedure to transpose a table structure into the form you suggested and then do my join with the item descriptions. This produces a result that I can then pivot and apply statistical analysis and reporting to using the descriptive headers in place of the itemID's. Awesome. Can't thank you enough.

Ed Sloat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources