March 6, 2014 at 10:06 pm
This is how data is in the table.
Column0 Column1 Column2 Column3Column4Column 5
2013 NULL NULL NULLNULL NULL
JED Jan Feb Mar Apr May
WT 24 9 11 10 9
L/B 1058 1859 1088986 1265
AHL 1212 93 434 1207 1170
DPR 96 61 74 67 77
Lost 333 333 382 331 353
RR 73 66 73 73 70
and it needs to be reorganized as below.
Year Month Station WT LB AHL DPR LOST RR
2013 Jan JED 24 1058 1212 96 333 73
2013 Feb JED 9 1859 993 61 333 66
March 6, 2014 at 10:10 pm
That is ia funny way to create your table.
I think you need to try the unpivot function.
March 6, 2014 at 10:13 pm
joeroshan (3/6/2014)
That is ia funny way to create your table.I think you need to try the unpivot function.
can you please help me writing the query....
March 6, 2014 at 11:25 pm
DECLARE @tbl TABLE
(
Col1 NVARCHAR(100),
Col2 NVARCHAR(100),
Col3 NVARCHAR(100),
Col4 NVARCHAR(100),
Col5 NVARCHAR(100),
Col6 NVARCHAR(100)
)
INSERT INTO @tbl
SELECT '2013', NULL, NULL, NULL,NULL, NULL UNION
SELECT 'JED', 'Jan', 'Feb', 'Mar','Apr', 'May'UNION
SELECT 'WT', '24', '9', '11', '10', '9' UNION
SELECT 'L/B', '1058', '1859', ' 1088', '986', ' 1265'UNION
SELECT 'AHL', '1212', ' 93', ' 434', ' 1207', ' 1170'UNION
SELECT 'DPR', '96', ' 61', ' 74', ' 67', ' 77'UNION
SELECT 'Lost', '333', ' 333', ' 382', ' 331', ' 353'UNION
SELECT 'RR', '73', ' 66', ' 73', ' 73', ' 70'
DECLARE @tblResult TABLE
(
[Year] INT,
[User] NVARCHAR(100),
Col3 NVARCHAR(100),
Col4 NVARCHAR(100),
Col5 NVARCHAR(100),
Col6 NVARCHAR(100)
)
DECLARE @Year INT,@User NVARCHAR(100)
SELECT @Year = Col1 FROM @tbl where COALESCE(Col2,Col3,Col4,Col5,Col6) IS NULL
SELECT @User = Col1 FROM @tbl t INNER JOIN sys.syslanguages Sy ON CHARINDEX(t.Col2,ShortMonths)>0 AND CHARINDEX(t.Col3,ShortMonths)>0
AND CHARINDEX(t.Col3,ShortMonths)>0 AND CHARINDEX(t.Col4,ShortMonths)>0 AND CHARINDEX(t.Col5,ShortMonths)>0 AND CHARINDEX(t.Col6,ShortMonths)>0
where langid = 0
DECLARE @tblMonth TABLE
(
ID INT,
[Month] NVARCHAR(MAX)
)
;with cte as
(
select Col1,Col2,1 as ID from @tbl
UNION
select Col1,Col3,2 as ID from @tbl
UNION
select Col1,Col4,3 as ID from @tbl
UNION
select Col1,Col5,4 as ID from @tbl
UNION
select Col1,Col6,5 as ID from @tbl
),cte2 as
(
Select c.Col1,c.Col2,C1.Col2 AS Month from cte c
INNER JOIN Cte c1 On C.ID = C1.ID
AND C1.col1 = @User
)
Select @Year AS [Year],* FROM
(
select Col1,[Month],Col2 from cte2
)D
PIVOT
(MAX(Col2) FOR COl1 IN (AHL,WT,[L/B],[DPR],[Lost],[RR])
)E
Regards,
Mitesh OSwal
+918698619998
March 6, 2014 at 11:34 pm
Thanks a lot Mitesh....I will try executing this query.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply