2

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 ?

shA.t
  • 16,580
  • 5
  • 54
  • 111
Ssis Magician 2014
  • 63
  • 1
  • 7
  • 3
    You asked, "Is there there any other option to get the desired view ?" No. Dynamic SQL is the only choice when you have a non deterministic number of columns in a pivot. – xQbert May 08 '15 at 21:01
  • 3
    You're basically asking if there is a way to perform a pivot without naming the columns individually, nor doing so with dynamic SQL. **I share your desire, and really wish there was a better option**, but unfortunately this has been asked before and answered here: http://stackoverflow.com/questions/2170058/can-sql-server-pivot-without-knowing-the-resulting-column-names – AaronLS May 08 '15 at 21:01
  • 2
    "*Is there a way to unpivot a dynamic set of categories without using Dynamic SQL?*" Yes. But not with T-SQL. Excel (and other tools) can do this just fine. If you want to do it on SQL Server, then as others have said, you need dynamic SQL. – RBarryYoung May 08 '15 at 21:12
  • 1
    Yep, and OLAP databases such as SQL Server Analysis Services specialize in grouping and pivoting using a different approach. They are good if you are going to be doing alot of this type of thing, but would be overkill for a one off query. – AaronLS May 08 '15 at 21:15
  • why to go for unpivot when wr are ging to achieve this same in using left join and derived table @Ssis Magician 2014 – mohan111 May 09 '15 at 04:23
  • Thanks for the replies. A dynamic unpivot sql seems to be unavoidable as the columns in table b are not fixed and will change each day. – Ssis Magician 2014 May 09 '15 at 13:44

1 Answers1

1

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
mohan111
  • 8,633
  • 4
  • 28
  • 55