Need Help in SQL query

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi Friends,
I have a table with data like below:

SQL

select * from Employee

E_Name     Jan    Feb    March    Apr    May
Amit       null   null   null     null   25000
Sharad     2000   null   null     null   null
Rohit      null   1000   null     null   null
Kishor     null   null   50000    null   null
Rahul      null   null   null     6000   null

And I want output to have records to show in which month Employee got the salary.
In my table shown above, E_Name, Jan, Feb, March, Apr, May are columns.
So my output should look like below :

SQL

E_Name   SalaryMonth
Amit     May
Sharad   Jan
Rohit    Feb
Kishor   March
Rahul    Apr

I tried to use schema but don't know where I am going wrong.
Any help Appreciated.

Thanks,
Lok..

Posted 22-Dec-13 22:26pm

Lokesh Zende

Add a Solution

5 solutions

Solution 3

I guess this will help

SQL

CREATE TABLE EmpSalary
(
EmpId int primary key,
EmpName varchar(50),
Jan int,
Feb int,
Mar int,
Apr int,
May int
)

INSERT INTO EmpSalary VALUES (1, 'Emp1', 10000, NULL, NULL, NULL, NULL)
INSERT INTO EmpSalary VALUES (2, 'Emp2', NULL, NULL, 5000, NULL, NULL)
INSERT INTO EmpSalary VALUES (3, 'Emp3', NULL, 15000, NULL, NULL, NULL)
INSERT INTO EmpSalary VALUES (4, 'Emp4', NULL, NULL, NULL, NULL, 25000)
INSERT INTO EmpSalary VALUES (5, 'Emp4', 10000, NULL, NULL, 12500, NULL)

select T1.EmpId,
       T1.EmpName,
       stuff((
             select ','+T2.company
             from (values(T1.Jan, 'Jan'),
                         (T1.Feb, 'Feb'),
                         (T1.Mar, 'Mar'),
                         (T1.Apr, 'Apr'),
                         (T1.May, 'May')
                         ) as T2(value, company)
             where T2.value IS NOT NULL
             for xml path('')
            ), 1, 1, '') as comp
from EmpSalary  as T1

Posted 22-Dec-13 23:21pm

Jobless Creature

Updated 23-Dec-13 0:48am

v2

Solution 5

SQL

SELECT E_Name,CASE WHEN ISNULL(Jan,0)!=0 THEN 'Jan' 
WHEN ISNULL(Feb,0)!=0 THEN 'Feb'
WHEN ISNULL(March,0)!=0 THEN 'March' WHEN ISNULL(Apr,0)!=0 THEN 'Apr'
WHEN ISNULL(May,0)!=0 THEN 'May' ELSE '--' END AS SalaryMonth
FROM dbo.Employee2

Posted 23-Dec-13 1:00am

Renuka Ruke

Updated 23-Dec-13 1:01am

v2

Solution 1

Do you have column for each month of a year....???

Try this

SQL

SELECT empname,'Jan'
  FROM   employee
  WHERE jan IS NOT NULL
  group by empname,jan
  HAVING COUNT(empname) >=1
UNION ALL
  
SELECT empname,'feb'
  FROM   employee
  WHERE feb IS NOT NULL
  group by empname,feb
  HAVING COUNT(empname) >=1

Posted 22-Dec-13 23:02pm

BrishPandey

Updated 23-Dec-13 0:48am

v3

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)

Maciej Los · Accepted Answer · 2013-12-23T02:58:00

All what you need to do is to UNPIVOT[^] data. Have a look at example:

SQL

DECLARE @emp TABLE (E_Name VARCHAR(30), Jan INT, Feb INT, March INT, Apr INT, May INT)

INSERT INTO @emp (E_Name, Jan, Feb, March, Apr, May)
VALUES('Amit', null, null, null, null, 25000),
('Sharad', 2000, null, null, null, null),
('Rohit', null, 1000, null, null, null),
('Kishor', null, null, 50000, null, null),
('Rahul', null, null, null, 6000, null)


SELECT Emp_Name, Month_Name, Salary
FROM (
    SELECT E_Name AS Emp_Name, Jan, Feb, March, Apr, May
    FROM @emp
    ) AS pvt
UNPIVOT (Salary FOR Month_Name IN ([Jan], [Feb], [March], [Apr], [May])) AS unpvt

Result:

Amit	May	25000
Sharad	Jan	2000
Rohit	Feb	1000
Kishor	March	50000
Rahul	Apr	6000

Ganesh Raja · Accepted Answer · 2013-12-22T23:06:00

Solution 2

If you have limited columns ,then u can use "case" like below,

SQL

SELECT [name],
case when jan is not null then 'jan' when feb is not null then 'feb' when mar is not null then 'mar' else '' end
FROM [testdb].[dbo].[tblemp]

Posted 22-Dec-13 23:06pm

Ganesh Raja

Comments

joginder-banger 23-Dec-13 9:00am

Good Job...Raja