February 25, 2009 at 10:11 pm
Hi to all , pleas help me......
i want transpose date from one table data columns to another table rows
Table 1 :CGPA
SIDNO AO101 BT101 CH101 ES101 TA101 PH101 MA101
06HYBT001 A A C B A D E
Table 2 : STUDENTREGDATA
SIDNO SUBID SEC
06HYBT001MA 101 1
06HYBT001PH 101 1
06HYBT001AO 101 1
06HYBT001ES 101 1
06HYBT001BT 101 1
I want out put like this type
Subid grades from table1
MA 101 E
PH 101 D
AO 101 A
ES 101 B
BT 101 A
i tryed this type
alter proc getdata
@col nvarchar(20),
@sidno nvarchar(20),
@subid nvarchar(20)
as
Declare @stmt nvarchar(500)
select @stmt=replace(@subid,' ','') from studentregdata where sidno like ''%'+@sidno+'%'''
set @stmt='Select '+@col+' from cgpa where sidno like ''%'+@sidno+'%'''
but this is not working propery ple help me any body...
thnks
February 25, 2009 at 10:24 pm
How about writing the query using UNION .
"Keep Trying"
February 27, 2009 at 12:05 pm
...or properly normalize the tables. You'd be amazed at how simple queries become when the tables are modeled correctly. It's kinda, like, y-know, the whole point of data modeling.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
March 1, 2009 at 11:06 pm
Yes thats right.
Better the design lesser the headaches later on.
"Keep Trying"
March 16, 2009 at 1:18 am
Hi,
Have you tried using UNPIVOT in sql2005? I hope this is what you are looking for.
SELECT SIDNO, SUBID, GRADES
FROM (
SELECT SIDNO, AO101, BT101, CH101, ES101, TA101, PH101, MA101
FROM CGPA
) as subCGPA
UNPIVOT ( GRADES FOR SUBID IN
(AO101, BT101, CH101, ES101, TA101, PH101, MA101)
) as subGrades
-- partial results: --
SIDNO SUBID GRADES
06HYBT001 AO101 A
06HYBT001 BT101 A
06HYBT001 CH101 C
06HYBT001 ES101 B
06HYBT001 TA101 A
06HYBT001 PH101 D
06HYBT001 MA101 E
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy