How to get result set in sql server

Question

0.00/5 (No votes)

See more:

Hello Everybody,

My result set is

SQL

emp_id   year  month   salary
------------------------------------
 1       2014   4     4500
 1       2014   5     5640
 1       2013   7     4530

Whether it is possible to get result like

SQL

emp_id    year    month     salary
-------------------------------------------
 1        2014     4         4500
                   5         5600

          2013     7         4530

in sql server 2008.

Thanks in Advance.

Posted 4-Mar-14 17:48pm

Member 10017719

Updated 4-Mar-14 18:01pm

Tom Marvolo Riddle

v3

Add a Solution

Comments

Krunal Rohit 4-Mar-14 23:49pm

Make it more clear to understand.

-KR

[no name] 5-Mar-14 0:18am

I think your problem is different. Can you please explain why you need that type of structure. Do you want to show in report like this way ? I can guess. Then there are another way.

Thanks!

Member 10017719 5-Mar-14 0:19am

ya Humayun Rashed

s#@!k 5-Mar-14 0:39am

hope you may need to show in UI/any as you specified pattern.
In sql server u can't get this type of result set because in emp_id column for the second record
what will be the expected value in your pattern.

2 solutions

Solution 2

This may look rather academic, but it is the key to solving the problem: the result set is one thing, the way it is displayed to the user is a different thing.
You get the correct result already, that is a good start. And now it is time to design a GUI in a programming language you are comfortable with where you can show it in the way desired.

Posted 4-Mar-14 20:20pm

Bernhard Hiller

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

george4986 · Accepted Answer · 2014-03-04T19:54:00

check this query simplify and build from this

ur table structure with values

SQL

CREATE TABLE employeeTemp (emp_id varchar(50), year varchar(50),month bigint,salary DECIMAL(6, 2))
INSERT INTO employeeTemp
VALUES   ('1','2014',4,4500),('1','2014',5,5600),('1','2013',7,4530)
;

select query

SQL

with temp as
(SELECT emp_id, year,month,salary, row_number() over(partition by emp_id order by emp_id) rn
FROM   employeeTemp as b)
SELECT Case WHEN rn>1 THEN '' ELSE emp_id END as emp_id,year,month,salary  into #temp from temp
order by  year desc
;
with temp1 as
(SELECT emp_id, year,month,salary, row_number() over(partition by year order by year) rn
FROM   #temp as b)
SELECT emp_id,Case WHEN rn>1 THEN '' ELSE year END as year,month,salary  from temp1
order by  month asc

good luck ;-)