January 4, 2011 at 1:58 pm
Hi guy's!!! Happy new year!!!
This time I have the following problem...
I have TableA, TableB and TableC. Same structure for the 3 tables:
ColumnName DataType
========== ========
Type char(1)
Code char(20)
Value numeric(19, 5)
Data is like this:
TableA
Type Code Value
==== ==== =====
A X 10
A Y 20
B X 30
B Y 40
C X 50
TableB
Type Code Value
==== ==== =====
A Z 11
A V 22
B Z 33
B V 44
C Z 55
TableC
Type Code Value
==== ==== =====
A M 111
A N 222
B M 333
B N 444
C M 555
How can I script to get this?
Type X Y Z V M N
==== === === === === === ===
A 10 20 11 22 111 222
B 30 40 33 44 333 444
C 50 0 55 0 555 0
I tried first joining Tables data into one view and then querying from it but I think there's a better way...
Thank's in advanced!!!
January 4, 2011 at 2:00 pm
Other than joining them together, the only thing I can think of is using Union to hook them together, then using Pivot/Unpivot to get them in the form you want. A simple Join query is going to be much easier, though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2011 at 3:32 pm
;WITH CTE (Type, Code, Value)
AS (
SELECT [Type], [Code], [Value]
FROM dbo.A
UNION ALL
SELECT Type, Code, Value
FROM dbo.B
UNION ALL
SELECT Type, Code, Value
FROM dbo.C
)
SELECT Type,
[X], [Y], [Z], [V], [M], [N]
FROM (
SELECT Type, Code, COALESCE(Value,0) as Value FROM CTE
) P
PIVOT
(
SUM(Value)
FOR Code IN ([X], [Y], [Z], [V], [M], [N])
) AS PivotTable;
January 4, 2011 at 4:28 pm
select Type, ISNULL([X],0), ISNULL([Y],0), ISNULL([Z],0), ISNULL([V],0), ISNULL([M],0), ISNULL([N],0)
From
(
select Type, Code, Value from TableA Union
select Type, Code, Value from TableB Union
select Type, Code, Value from TableC
) as p
Pivot
(
avg(value)
For [Code] in ([X], [Y], [Z], [V] , [M], [N])
) as pt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply