display the values hori. to vertical

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

display the values hori. to vertical

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2015-02-09 : 08:10:43

My query is
SELECT t.subjectname,s.regno,s.fname,s.lname,a.totresult FROM assessment a inner join student s inner join subject t on s.regno=a.sturegno and a.subcode=t.subjectcode and s.class='BCA' and s.year='II'

I get the output as

subjectname regno fname lname totresult
Tamil stu1234 vidhya shri 12
English stu1234 vidhya shri 25
Java stu1234 vidhya shri 23

But,
I need the output as

regno fname lname tamil english java
stu1234 vidhya shri 12 25 23

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 08:44:53

[code]
SELECT s.regno
, s.fname
, s.lname
, CASE
WHEN t.subjectname = 'Tamil'
THEN a.totresult
END AS 'Tamil'
, CASE
WHEN t.subjectname = 'English'
THEN a.totresult
END AS 'English'
, CASE
WHEN t.subjectname = 'Java'
THEN a.totresult
END AS 'Java'
FROM assessment a
INNER JOIN student s
INNER JOIN [subject] t ON s.regno = a.sturegno
AND a.subcode = t.subjectcode
AND s.class = 'BCA'
AND s.[year] = 'II'
[/code]

vidhya
Posting Yak Master

108 Posts

Posted - 2015-02-09 : 08:58:03

It dispalys the value like this
egno fname lname Tamil English Java
stu1234 vidhya shri 12 NULL NULL
stu1234 vidhya shri NULL 25 NULL
stu1234 vidhya shri NULL NULL 23

but i need to display in a single row of each student according to the regno.

stu1234 vidhya shri 12 25 23

U need mention tamil, english in query ,these values are fetched from database.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 09:03:56

The solution to your first problem is to group the results like this:


SELECT s.regno
     , s.fname
     , s.lname
     , max(CASE 
          WHEN t.subjectname = 'Tamil'
               THEN a.totresult
          END) AS 'Tamil'
     , max(CASE 
          WHEN t.subjectname = 'English'
               THEN a.totresult
          END) AS 'English'
     , max(CASE 
          WHEN t.subjectname = 'Java'
               THEN a.totresult
          END) AS 'Java'
FROM assessment a
INNER JOIN student s
INNER JOIN [subject] t ON s.regno = a.sturegno
     AND a.subcode = t.subjectcode
     AND s.class = 'BCA'
     AND s.[year] = 'II'
group by s.regno
     , s.fname
     , s.lname

The solution to your second problem (note, you did not say anything about having more languages in your original question) is to use dynamic SQL.

vidhya
Posting Yak Master

108 Posts

Posted - 2015-02-09 : 09:17:17

now it display in single row , but subject name tamil, english, java is not constant, it may vary. how is it possible?

max(CASE
WHEN t.subjectname = 'Tamil'
THEN a.totresult
END) AS 'Tamil'
, max(CASE
WHEN t.subjectname = 'English'
THEN a.totresult
END) AS 'English'
, max(CASE
WHEN t.subjectname = 'Java'
THEN a.totresult
END) AS 'Java'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 09:22:28

You will have to build the query as a string (NVARCHAR) then execute it as dynamic SQL.

If you post your table definitions (CREATE TABLE statements) and some sample data (INSERT INTO statements), we can show you how to do that.

vidhya
Posting Yak Master

108 Posts

Posted - 2015-02-09 : 09:30:56

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Feb 09, 2015 at 03:29 PM
-- Server version: 5.6.21
-- PHP Version: 5.5.19

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `internal`
--

-- --------------------------------------------------------

--
-- Table structure for table `assessment`
--

CREATE TABLE IF NOT EXISTS `assessment` (
`assesmentid` int(11) NOT NULL,
`staregno` varchar(30) NOT NULL,
`sturegno` varchar(30) NOT NULL,
`subcode` varchar(100) NOT NULL,
`assign1` int(30) NOT NULL,
`assign2` int(30) NOT NULL,
`totassign` int(30) NOT NULL,
`avgassign` float NOT NULL,
`seminor1` int(30) NOT NULL,
`seminor2` int(30) NOT NULL,
`totseminor` int(30) NOT NULL,
`avgseminor` float NOT NULL,
`unittest1` int(40) NOT NULL,
`unittest2` int(30) NOT NULL,
`unittest3` int(30) NOT NULL,
`unittest4` int(30) NOT NULL,
`unittest5` int(30) NOT NULL,
`avgunittest` float NOT NULL,
`totresult` int(30) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `assessment`
--

INSERT INTO `assessment` (`assesmentid`, `staregno`, `sturegno`, `subcode`, `assign1`, `assign2`, `totassign`, `avgassign`, `seminor1`, `seminor2`, `totseminor`, `avgseminor`, `unittest1`, `unittest2`, `unittest3`, `unittest4`, `unittest5`, `avgunittest`, `totresult`) VALUES
(1, 'sta123', 'stu1233', 'p11mca14', 10, 10, 20, 5, 10, 10, 20, 5, 10, 10, 10, 10, 10, 2.72727, 13),
(2, 'sta123', 'stu1234', 'RLCT3', 8, 7, 15, 3.75, 7, 7, 14, 3.5, 13, 16, 15, 14, 19, 4.28571, 12),
(3, 'sta123', 'stu1234', 'RECLE3', 10, 10, 20, 5, 10, 10, 20, 5, 48, 48, 48, 46, 74, 14.5714, 25),
(4, 'sta123', 'stu1234', 'RCCS10CA3', 10, 10, 20, 5, 10, 10, 20, 5, 32, 35, 48, 11, 71, 13.2, 23);

-- --------------------------------------------------------

--
-- Table structure for table `login`
--

CREATE TABLE IF NOT EXISTS `login` (
`loginid` int(10) NOT NULL,
`username` varchar(10) NOT NULL,
`pass` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `login`
--

INSERT INTO `login` (`loginid`, `username`, `pass`) VALUES
(1, 'admin', 'admin');

-- --------------------------------------------------------

--
-- Table structure for table `staff`
--

CREATE TABLE IF NOT EXISTS `staff` (
`sid` int(100) NOT NULL,
`regno` varchar(50) NOT NULL,
`fname` char(100) NOT NULL,
`lname` char(100) NOT NULL,
`qual` char(100) NOT NULL,
`desg` char(100) NOT NULL,
`dob` varchar(100) NOT NULL,
`gender` char(100) NOT NULL,
`address` varchar(100) NOT NULL,
`status` char(100) NOT NULL,
`contact` int(100) NOT NULL,
`emailid` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`sid`, `regno`, `fname`, `lname`, `qual`, `desg`, `dob`, `gender`, `address`, `status`, `contact`, `emailid`, `username`, `password`) VALUES
(1, 'sta123', 'vidhya', 'shri', 'MCA.,M.tech', 'Assistant prof in cs', '28/5/82', 'Female', 'sholapuram', 'Married', 2536, 'a@hotmail.com', 'vidhya', 'shri');

-- --------------------------------------------------------

--
-- Table structure for table `student`
--

CREATE TABLE IF NOT EXISTS `student` (
`sid` int(100) NOT NULL,
`regno` varchar(40) NOT NULL,
`fname` char(100) NOT NULL,
`lname` char(100) NOT NULL,
`pname` char(100) NOT NULL,
`batch` char(100) NOT NULL,
`class` char(100) NOT NULL,
`dept` char(100) NOT NULL,
`year` char(100) NOT NULL,
`dob` varchar(100) NOT NULL,
`gender` char(100) NOT NULL,
`address` varchar(100) NOT NULL,
`status` char(100) NOT NULL,
`contact` int(100) NOT NULL,
`emailid` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`sid`, `regno`, `fname`, `lname`, `pname`, `batch`, `class`, `dept`, `year`, `dob`, `gender`, `address`, `status`, `contact`, `emailid`, `username`, `password`) VALUES
(1, 'stu1233', 'kavi', 'ram', 'raj', '2014', 'MCA', 'Department', 'II', '28/5/81', 'Female', 'kumbkonam', 'Married', 2147483647, 'a@shri', 'kavi', 'ram'),
(2, 'stu1234', 'vidhya', 'shri', 'balu', '2013', 'BCA', 'Computer Science', 'II', '28/5/82', 'Female', 'sholapuram', 'Married', 2147483647, 'a@shri', 'vidhya', 'shri');

-- --------------------------------------------------------

--
-- Table structure for table `subject`
--

CREATE TABLE IF NOT EXISTS `subject` (
`subid` int(100) NOT NULL,
`subjectcode` varchar(100) NOT NULL,
`subjectname` varchar(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `subject`
--

INSERT INTO `subject` (`subid`, `subjectcode`, `subjectname`) VALUES
(1, 'p11mca14', 'j2ee'),
(2, 'p11mca15', 'web technology'),
(3, 'RLCT3', 'Tamil'),
(4, 'RECLE3', 'English'),
(5, 'RCCS10CA3', 'Java'),
(6, 'RACSY57A', 'Accounting');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `assessment`
--
ALTER TABLE `assessment`
ADD PRIMARY KEY (`assesmentid`);

--
-- Indexes for table `login`
--
ALTER TABLE `login`
ADD PRIMARY KEY (`loginid`);

--
-- Indexes for table `staff`
--
ALTER TABLE `staff`
ADD PRIMARY KEY (`sid`);

--
-- Indexes for table `student`
--
ALTER TABLE `student`
ADD PRIMARY KEY (`sid`);

--
-- Indexes for table `subject`
--
ALTER TABLE `subject`
ADD PRIMARY KEY (`subid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `assessment`
--
ALTER TABLE `assessment`
MODIFY `assesmentid` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `login`
--
ALTER TABLE `login`
MODIFY `loginid` int(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `staff`
--
ALTER TABLE `staff`
MODIFY `sid` int(100) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `student`
--
ALTER TABLE `student`
MODIFY `sid` int(100) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `subject`
--
ALTER TABLE `subject`
MODIFY `subid` int(100) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 09:36:54

aha! I see this is MySql, correct? Unfortunately this is SQL Server-only forum. You might want to try the MySql forums

vidhya
Posting Yak Master

108 Posts

Posted - 2015-02-09 : 09:44:58

sql server got problem so i am working in mysql. so u tell in sql server. i try in mysql and check it.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 10:20:07

First, please translate your CREATE TABLE statements to SQL Server.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-09 : 14:02:27

I am by far no pivot expert, but I think this might work:

select *
  from (select t.subjectname
              ,s.regno
              ,s.fname
              ,s.lname
              ,a.totresult
          from assessment as a
               inner join student as s
                       on s.regno=a.sturegno
               inner join subject as t
                       on t.subjectcode=a.subcode
         where s.class='BCA'
           and s.[year]='II'
       ) as a
 pivot (sum(totresult)
        for subjectname in (Accounting,English,j2ee,Java,Tamil,[web technology])
       ) as p

If you don't want to maintain the subjectnames in the pivot section manually, you have to make this query dynamic.
Also this is for mssql - you have to convert it to mysql yourself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources