Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot Issue

Author  Topic 

asif372
Posting Yak Master

100 Posts

Posted - 2013-01-24 : 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
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 @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
[/code]

--
Chandu
Go to Top of Page

asif372
Posting Yak Master

100 Posts

Posted - 2013-01-24 : 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
Go to Top of Page

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 @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
Go to Top of Page

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 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
   

- Advertisement -