how to make this sql query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

hi all,

Help plz

I have a table like follows:

EmpID	01-Apr-2011	02-Apr-2011	03-Apr-2011	04-Apr-2011	05-Apr-2011

1	Shift1	Shift2	Shift3	Shift4	Shift5
2	Shift1	Shift2	Shift3	Shift4	Shift5
3	Shift1	Shift2	Shift3	Shift4	Shift5
4	Shift1	Shift2	Shift3	Shift4	Shift5
5	Shift1	Shift2	Shift3	Shift4	Shift5

The dates in above table are actual field names.

I have created another temporary table like follows:

EmpID	Dates	        

1	1-Apr-2011	
1	2-Apr-2011	
1	3-Apr-2011	
1	4-Apr-2011	
1	5-Apr-2011	
2	5-Apr-2011	
2	4-Apr-2011	
2	3-Apr-2011	
2	2-Apr-2011	
2	1-Apr-2011	
3	1-Apr-2011	
3	2-Apr-2011	
3	3-Apr-2011	
3	4-Apr-2011	
3	5-Apr-2011	
4	5-Apr-2011	
4	4-Apr-2011	
4	3-Apr-2011	
4	2-Apr-2011	
4	1-Apr-2011	
5	1-Apr-2011	
5	2-Apr-2011	
5	3-Apr-2011	
5	4-Apr-2011	
5	5-Apr-2011

I am able to generate above records but I also need to generate the shift as well using the first table like follows:

EmpID	Dates	        Shift

1	1-Apr-2011	Shift1
1	2-Apr-2011	Shift2
1	3-Apr-2011	Shift3
1	4-Apr-2011	Shift4
1	5-Apr-2011	Shift5
2	5-Apr-2011	Shift1
2	4-Apr-2011	Shift2
2	3-Apr-2011	Shift3
2	2-Apr-2011	Shift4
2	1-Apr-2011	Shift5
3	1-Apr-2011	Shift1
3	2-Apr-2011	Shift2
3	3-Apr-2011	Shift3
3	4-Apr-2011	Shift4
3	5-Apr-2011	Shift5
4	5-Apr-2011	Shift1
4	4-Apr-2011	Shift2
4	3-Apr-2011	Shift3
4	2-Apr-2011	Shift4
4	1-Apr-2011	Shift5
5	1-Apr-2011	Shift1
5	2-Apr-2011	Shift2
5	3-Apr-2011	Shift3
5	4-Apr-2011	Shift4
5	5-Apr-2011	Shift5

does any body know how can i achieve this.

Regards,
Gopal

Posted 6-May-11 2:03am

gopalgupta

Updated 6-May-11 5:56am

pankajupadhyay29

v2

Add a Solution

3 solutions

Solution 3

Making it more dynamic...there might be few thing we can eliminate....rite now its just a working idea
CREATE TABLE dbo.EmpDate_Shift
(
EMPID INT,
[01-Apr-2011] VARCHAR(50),
[02-Apr-2011] VARCHAR(50),
[03-Apr-2011] VARCHAR(50),
[04-Apr-2011] VARCHAR(50),
[05-Apr-2011] VARCHAR(50),
[06-Apr-2011] VARCHAR(50)
)

INSERT INTO EmpDate_Shift
SELECT 1,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'
UNION ALL SELECT 2,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'
UNION ALL SELECT 3,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'
UNION ALL SELECT 4,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'
UNION ALL SELECT 5,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'

CREATE TABLE dbo.EmpDate
(
EMPID INT,
Dates VARCHAR(50)
)

INSERT INTO EmpDate
SELECT 1, '1-Apr-2011'
UNION ALL SELECT 1, '2-Apr-2011'
UNION ALL SELECT 1, '3-Apr-2011'
UNION ALL SELECT 1, '4-Apr-2011'
UNION ALL SELECT 1, '5-Apr-2011'
UNION ALL SELECT 1, '6-Apr-2011'
UNION ALL SELECT 2, '1-Apr-2011'
UNION ALL SELECT 2, '2-Apr-2011'
UNION ALL SELECT 2, '3-Apr-2011'
UNION ALL SELECT 2, '4-Apr-2011'
UNION ALL SELECT 2, '5-Apr-2011'
UNION ALL SELECT 2, '6-Apr-2011'
UNION ALL SELECT 3, '1-Apr-2011'
UNION ALL SELECT 3, '2-Apr-2011'
UNION ALL SELECT 3, '3-Apr-2011'
UNION ALL SELECT 3, '4-Apr-2011'
UNION ALL SELECT 3, '5-Apr-2011'
UNION ALL SELECT 3, '6-Apr-2011'
UNION ALL SELECT 4, '1-Apr-2011'
UNION ALL SELECT 4, '2-Apr-2011'
UNION ALL SELECT 4, '3-Apr-2011'
UNION ALL SELECT 4, '4-Apr-2011'
UNION ALL SELECT 4, '5-Apr-2011'
UNION ALL SELECT 4, '6-Apr-2011'
UNION ALL SELECT 5, '1-Apr-2011'
UNION ALL SELECT 5, '2-Apr-2011'
UNION ALL SELECT 5, '3-Apr-2011'
UNION ALL SELECT 5, '4-Apr-2011'
UNION ALL SELECT 5, '5-Apr-2011'
UNION ALL SELECT 5, '6-Apr-2011'

SELECT Name As Date INTO EmpDateCol
FROM syscolumns WHERE id IN (SELECT object_id FROM sys.tables WHERE name = 'EmpDate_Shift')
AND ISDate(Name) = 1

CREATE TABLE dbo.EmpDateShift
(
EmpId INT,
Dates VARCHAR(25),
Shift VARCHAR(25)
)
--SELECT * FROM EmpDateCol
--SELECT * FROM EmpDate
--SELECT * FROM EmpDate_Shift

DECLARE @Sql VARCHAR(8000), @Val VARCHAR(50)
SET @Sql = ''

DECLARE cur_Date CURSOR FOR SELECT [Date] FROM EmpDateCol
OPEN cur_Date
FETCH NEXT FROM cur_Date INTO @Val

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Sql = @Sql + 'SELECT B.EmpID,'''+ @Val + ''',C.[' + @Val + '] FROM EmpDateCol A INNER JOIN Fankarian..EmpDate B ON CONVERT(Date,A.Date) = CONVERT(Date,B.Dates)
INNER JOIN EmpDate_Shift C ON C.Empid = B.EmpID WHERE CONVERT(Date,B.Dates) = CONVERT(Date,''' + @Val + ''') UNION ALL' + CHAR(13)

--INSERT INTO EmpDateShift
--EXEC (@Sql)

FETCH NEXT FROM cur_Date INTO @Val
END

CLOSE cur_Date
DEALLOCATe cur_Date

SET @Sql = LEFT(@Sql,LEN(@Sql)-10)

INSERT INTO EmpDateShift
EXEC (@Sql)

SELECT * FROM EmpDateShift ORDER BY EmpID, Dates

DROP TABLE EmpDateShift
DROP TABLE EmpDate_Shift
DROP TABLE EmpDate
DROP TABLE EmpDateCol

Posted 10-May-11 7:36am

mhwasim

Solution 2

You can solve this using Unpivot for details see this[^]

this will fast.

Posted 6-May-11 6:06am

pankajupadhyay29

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)

Umair Feroze · Accepted Answer · 2011-05-06T05:56:00

For your query let me say table 1 of yours "Emp" and temp table as "EmpDet". Then try the following query:

SQL

SELECT B.*,
        CASE
            WHEN B.Dates = '1-Apr-2011' THEN A.[01-Apr-2011]
            WHEN B.Dates = '2-Apr-2011' THEN A.[02-Apr-2011]
            WHEN B.Dates = '3-Apr-2011' THEN A.[03-Apr-2011]
            WHEN B.Dates = '4-Apr-2011' THEN A.[04-Apr-2011]
            WHEN B.Dates = '5-Apr-2011' THEN A.[05-Apr-2011]
        END
FROM Emp A
JOIN EmpDet B ON A.EMPId = B.EMPID

Hope this answer your question