CODEWITHSUNDEEP
sqlsql-server
Ssis Magician 2014
Ssis Magician 2014

Reputation: 63

Flipping data in SQL Server

I have table a

Name  District Source  Revenue
Dave   34       A       120
John   36       B       140
Juyt   38       C       170

And table b

Name District Product Cost
Dave  34       A        50
John  36       B        40

I want a view like so. Desired View below.

Name  District Source  Revenue  A    B   Total Cost 
Dave   34       A       120     50   0     50
John   36       B       140     0    40    40 
Juyt   38       C       170     0    0     0

The number of products is not fixed for each lookup in table b. Is there a way to unpivot when you the number of products like Product A, Product B are not fixed. I do not want to do dynamic SQL and a dynamic unpivot. Is there there any other option to get the desired view ?

Upvotes: 2

Views: 115

Answers (1)

mohan111
mohan111

Reputation: 8865

basing on your sample data i have given you the output 

declare @t table (name varchar(10),District int,Source varchar(2),Revenue int)
insert into @t (name,District,Source,Revenue)values ('dave',34,'A',120),
('john',36,'B',140),('juyt',38,'C',170)

declare @tt table (name varchar(10),District int,product varchar(2),cost int)
insert into @tt (name,District,product,cost)values ('dave',34,'A',50),
('john',36,'B',40)


select A.name,A.District,A.Source,A.Revenue,A.A,A.B,
SUM(A + B) TotalCost from  (
select t.name,
t.District,
t.Source,
t.Revenue,
CASE WHEN tt.product = 'A' THEN cost ELSE 0 END A ,
CASE WHEN tt.product = 'B' THEN cost ELSE 0 END B 
 from @t t
 left join @tt tt on 
 t.name = tt.name 
 AND 
 t.District = tt.District )A
 GROUP BY A.name,A.District,A.Source,A.Revenue,A.A,A.B

Upvotes: 1

Related Questions