SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot Issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 01/24/2013 :  01:09:36  Show Profile  Reply with Quote
My Data is like this

Year Month D1 D2 D3 D4 D5 D6
2013 01 G G A L L C
2013 02 A P E V O B
2013 03 B O L N M O
2013 04 A R Q Z E P

I Want Data like this

Year Month Date remarks
2013 01 01 G
2013 01 02 G
2013 01 03 A
2013 01 04 L
2013 01 05 L
2013 01 06 C

2013 02 01 A
2013 02 02 P
2013 02 03 E
2013 02 04 V
2013 02 05 O
2013 02 06 B
how can it be possible
Thanks in Advance

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 01/24/2013 :  01:22:03  Show Profile  Reply with Quote
DECLARE @t TABLE(Year int, Month int, D1 char(1), D2 char(1), D3 char(1), D4 char(1), D5 char(1), D6 char(1))
insert into @t
SELECT 2013, 01, 'G', 'G', 'A', 'L', 'L', 'C' union all
SELECT 2013, 02, 'A', 'P', 'E', 'V', 'O', 'B' union all
SELECT 2013, 03, 'B', 'O', 'L', 'N', 'M', 'O' union all
SELECT 2013, 04, 'A', 'R', 'Q', 'Z', 'E', 'P'

SELECT Year, Month, '0'+RIGHT(Col, 1) Date, remarks
FROM @t 
UNPIVOT (remarks FOR Col IN ([D1], [D2], [D3], [D4], [D5], [D6]))u


--
Chandu
Go to Top of Page

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 01/24/2013 :  01:57:49  Show Profile  Reply with Quote
Bandi thanks for your responce
it works but fine but when i have records for multiple months and year the data conflict i think condition should be included
my query is as follows


SELECT
Pin_Code EID,
Year,
Month,
--'0'+RIGHT(Col, 1) Date, remark
RIGHT(Col, 1) Date, remark
from
ROSTER
UNPIVOT (remark FOR Col IN ([d1], [d2], [d3], [d4], [d5], [d6] , [d7] , [d8], [d9], [d10],
[d11], [d12], [d13], [d14], [d15], [d16], [d17], [d18], [d19], [d20],
[d21], [d22], [d23], [d24], [d25], [d26], [d27], [d28], [d29], [d30],[d31]))u
Where
Pin_Code = 17039
AND year = 2012
AND MONTH = 08
Order by Date

i receive the following data i only need one row for one date 1 but it show multiple records

EID-----Year----month---Date----Remarks
17039 2012 08 0 RA
17039 2012 08 0 O
17039 2012 08 0 G
17039 2012 08 1 G
17039 2012 08 1 O
17039 2012 08 1 RA
17039 2012 08 1 RA
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 01/24/2013 :  02:24:33  Show Profile  Reply with Quote
DECLARE @t TABLE(Year int, Month int, D1 char(1), D2 char(1), D3 char(1), D4 char(1), D5 char(1), D16 char(1))
insert into @t
SELECT 2013, 01, 'G', 'G', 'A', 'L', 'L', 'C' union all
SELECT 2013, 02, 'A', 'P', 'E', 'V', 'O', 'B' union all
SELECT 2013, 03, 'B', 'O', 'L', 'N', 'M', 'O' union all
SELECT 2013, 04, 'A', 'R', 'Q', 'Z', 'E', 'P'

SELECT Year, Month, RIGHT(REPLACE(Col, 'D', '0'), 2) Date, remarks
FROM @t
UNPIVOT (remarks FOR Col IN ([D1], [D2], [D3], [D4], [D5], [D16]))u

--
Chandu

Edited by - bandi on 01/24/2013 02:30:38
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/25/2013 :  00:07:03  Show Profile  Reply with Quote
quote:
Originally posted by asif372

Bandi thanks for your responce
it works but fine but when i have records for multiple months and year the data conflict i think condition should be included
my query is as follows


SELECT
Pin_Code EID,
Year,
Month,
--'0'+RIGHT(Col, 1) Date, remark
RIGHT(Col, 1) Date, remark
from
ROSTER
UNPIVOT (remark FOR Col IN ([d1], [d2], [d3], [d4], [d5], [d6] , [d7] , [d8], [d9], [d10],
[d11], [d12], [d13], [d14], [d15], [d16], [d17], [d18], [d19], [d20],
[d21], [d22], [d23], [d24], [d25], [d26], [d27], [d28], [d29], [d30],[d31]))u
Where
Pin_Code = 17039
AND year = 2012
AND MONTH = 08
Order by Date

i receive the following data i only need one row for one date 1 but it show multiple records

EID-----Year----month---Date----Remarks
17039 2012 08 0 RA
17039 2012 08 0 O
17039 2012 08 0 G
17039 2012 08 1 G
17039 2012 08 1 O
17039 2012 08 1 RA
17039 2012 08 1 RA



But you've multiple Remarks existing for same date.
In that case how do you want to select remarks value? you want just one or all of them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000