Author |
Topic |
asif372
Posting Yak Master
100 Posts |
Posted - 2013-01-24 : 01:09:36
|
My Data is like thisYear Month D1 D2 D3 D4 D5 D62013 01 G G A L L C2013 02 A P E V O B2013 03 B O L N M O2013 04 A R Q Z E PI Want Data like thisYear Month Date remarks2013 01 01 G2013 01 02 G2013 01 03 A2013 01 04 L2013 01 05 L2013 01 06 C2013 02 01 A2013 02 02 P2013 02 03 E2013 02 04 V2013 02 05 O2013 02 06 Bhow can it be possibleThanks in Advance |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-24 : 01:22:03
|
[code]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 @tSELECT 2013, 01, 'G', 'G', 'A', 'L', 'L', 'C' union allSELECT 2013, 02, 'A', 'P', 'E', 'V', 'O', 'B' union allSELECT 2013, 03, 'B', 'O', 'L', 'N', 'M', 'O' union allSELECT 2013, 04, 'A', 'R', 'Q', 'Z', 'E', 'P'SELECT Year, Month, '0'+RIGHT(Col, 1) Date, remarksFROM @t UNPIVOT (remarks FOR Col IN ([D1], [D2], [D3], [D4], [D5], [D6]))u[/code]--Chandu |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2013-01-24 : 01:57:49
|
Bandi thanks for your responceit 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 followsSELECTPin_Code EID,Year,Month,--'0'+RIGHT(Col, 1) Date, remarkRIGHT(Col, 1) Date, remarkfrom ROSTERUNPIVOT (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]))uWherePin_Code = 17039AND year = 2012AND MONTH = 08Order by Datei receive the following data i only need one row for one date 1 but it show multiple recordsEID-----Year----month---Date----Remarks17039 2012 08 0 RA17039 2012 08 0 O17039 2012 08 0 G17039 2012 08 1 G17039 2012 08 1 O17039 2012 08 1 RA17039 2012 08 1 RA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-24 : 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 @tSELECT 2013, 01, 'G', 'G', 'A', 'L', 'L', 'C' union allSELECT 2013, 02, 'A', 'P', 'E', 'V', 'O', 'B' union allSELECT 2013, 03, 'B', 'O', 'L', 'N', 'M', 'O' union allSELECT 2013, 04, 'A', 'R', 'Q', 'Z', 'E', 'P'SELECT Year, Month, RIGHT(REPLACE(Col, 'D', '0'), 2) Date, remarksFROM @t UNPIVOT (remarks FOR Col IN ([D1], [D2], [D3], [D4], [D5], [D16]))u--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 00:07:03
|
quote: Originally posted by asif372 Bandi thanks for your responceit 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 followsSELECTPin_Code EID,Year,Month,--'0'+RIGHT(Col, 1) Date, remarkRIGHT(Col, 1) Date, remarkfrom ROSTERUNPIVOT (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]))uWherePin_Code = 17039AND year = 2012AND MONTH = 08Order by Datei receive the following data i only need one row for one date 1 but it show multiple recordsEID-----Year----month---Date----Remarks17039 2012 08 0 RA17039 2012 08 0 O17039 2012 08 0 G17039 2012 08 1 G17039 2012 08 1 O17039 2012 08 1 RA17039 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|