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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply