| Author |
 Topic  |
|
|
asif372
Yak Posting Veteran
Pakistan
90 Posts |
 Posted - 01/24/2013 : 01:09:36
|
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
1407 Posts |
Posted - 01/24/2013 : 01:22:03
|
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 |
 |
|
|
asif372
Yak Posting Veteran
Pakistan
90 Posts |
Posted - 01/24/2013 : 01:57:49
|
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
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1407 Posts |
Posted - 01/24/2013 : 02:24:33
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/25/2013 : 00:07:03
|
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/
|
|
|
| |
Topic |
|
|
|
Pivot Issue
