May 5, 2011 at 1:17 am
Hi friends,
I am having a table create statement is:
create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(15),
Stu_Class varchar(10), sub_id varchar(2), marks varchar(3));
with following data:
insert into Stu_Table values(1,'Komal',10,1,45);
insert into Stu_Table values(2,'Ajay',10,1,56);
insert into Stu_Table values(3,'Rakesh',10,1,67);
insert into Stu_Table values(1,'Komal',10,2,47);
insert into Stu_Table values(2,'Ajay',10,2,53);
insert into Stu_Table values(3,'Rakesh',10,2,57);
insert into Stu_Table values(1,'Komal',10,3,45);
insert into Stu_Table values(2,'Ajay',10,3,56);
insert into Stu_Table values(3,'Rakesh',10,3,67);
insert into Stu_Table values(1,'Komal',10,4,65);
insert into Stu_Table values(2,'Ajay',10,4,56);
insert into Stu_Table values(3,'Rakesh',10,4,37);
insert into Stu_Table values(1,'Komal',10,5,65);
insert into Stu_Table values(2,'Ajay',10,5,46);
insert into Stu_Table values(3,'Rakesh',10,5,63);
select statement is looks like
+--------+----------+-----------+--------+-------+
| Stu_Id | Stu_Name | Stu_Class | sub_id | marks |
+--------+----------+-----------+--------+-------+
| 1 | Komal | 10 | 1 | 45 |
| 2 | Ajay | 10 | 1 | 56 |
| 3 | Rakesh | 10 | 1 | 67 |
| 1 | Komal | 10 | 2 | 47 |
| 2 | Ajay | 10 | 2 | 53 |
| 3 | Rakesh | 10 | 2 | 57 |
| 1 | Komal | 10 | 3 | 45 |
| 2 | Ajay | 10 | 3 | 56 |
| 3 | Rakesh | 10 | 3 | 67 |
| 1 | Komal | 10 | 4 | 65 |
| 2 | Ajay | 10 | 4 | 56 |
| 3 | Rakesh | 10 | 4 | 37 |
| 1 | Komal | 10 | 5 | 65 |
| 2 | Ajay |10 | 5 | 46 |
| 3 | Rakesh | 10 | 5 | 63 |
+--------+----------+-----------+--------+-------+
and i am having another table the create syntax is as follows:
CREATE TABLE [dbo].[TB_SubjectMaster](
[id] [int] IDENTITY(1,1) NOT NULL,
[Subject_code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Subject] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Practical] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_TB_SubjectMaster_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
------------------------------
data in TB_SubjectMaster is as follows::
insert into TB_SubjectMaster values(1,'Chem_01','Chemistry','Y')
insert into TB_SubjectMaster values(2,'Phy_01','Physics','Y')
insert into TB_SubjectMaster values(3,'Math_01','Maths','N')
insert into TB_SubjectMaster values(4,'Eng_01','English','N')
insert into TB_SubjectMaster values(5,'Science_01','Science','N')
now i want to display record in following manner:
+--------+----------+-----------+--------+---------+------------+-------------+---------------
| Stu_Id | Stu_Name | Stu_Class | Chemistry|Physics | Maths |English |Science
+--------+----------+-----------+--------+---------+------------+---------------------------------
| 1 | Komal | 10 | 45 | 47 |45 | 65 | 65
| 2 | Ajay | 10 | 56 | 53 |56 | 56 | 46
| 3 | Rakesh | 10 |67 | 57 |67 | 37 | 63
+--------+----------+-----------+--------+-------+-----------------------------------------------
Any one have an idea to do this by any method select statement or view or by any stored procedure
Thanks !!
May 5, 2011 at 1:51 am
You could either use PIVOT or the CrossTab approach.
For the former, please have a look at BOL, the concept of the latter is referenced in my signature block.
As a side note: since I'm assuming it's some sort of homework, I'm not providing a coded possible solution...
Give it a try, see how far you can get and post back if you have specific questions. We'll be more than glad to try to help and/or explain.
May 5, 2011 at 2:14 am
what is PIVOT Approch..
plz describe it by an example plz plz help me..
i have never use this approch ..plz send some documentation related to PIVOT approch..
Thanks!!
May 5, 2011 at 2:23 am
Did you look at Books Online (the SQL help file) as Lutz suggested?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply