Please advise how to use SQL statements Atable into Btable?
Dear great god looks the problem seems to be a little too simple, but also please feel free to comment, appreciate

The Table A
Team Cost Type Cost
A Business 20
Capital, 30 B
External 28 C
D Business 35
A Capital 25
B Business 29
Business 39 C
D External 44


Table B
The Team Business Capital External
A 20 to 25 0
B, 29 and 30 0
39 0 28 C
35 0 44 D

CodePudding user response:

A kind of method is:

The select Team,
Max (case CostType when 'Business' then cost else 0 end) as' Business ',
Max (case CostType the when the 'Capital' then cost else 0 end) as the 'Capital',
Max (case CostType when 'External' then cost else 0 end) as' External '
The from TableA
Group by the Team.

Actually have a special way to deal with the problems, are interested you can further understand the PIVOT


The select Team, ISNULL ([Business], 0) as' Business 'ISNULL ([Capital], 0) as the' Capital ', ISNULL ([External], 0) as' External 'from TableA a
The PIVOT
(
Max (a.c ost) for a.c ostType (in [Business], [Capital], [External])
B);

CodePudding user response:

If Cost Type fixed that several, can use the static method, if not fixed can use dynamic
https://blog.csdn.net/sinat_28984567/article/details/79865046
The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T ([Team] nvarchar (21), [CostType] nvarchar (28), [Cost] int) 
Insert # T 
Select N 'A', N 'Business', 20 union all 
Select N 'B', N 'Capital', 30 union all 
Select N 'C', N 'External', 28 union all 
Select N 'D', N 'Business', 35 union all 
Select N 'A', N 'Capital', 25 union all 
Select N 'B', N 'Business', 29 union all 
Select N 'C', N 'Business', 39 union all 
Select N 'D', N 'External', 44 
Go 
- the end of the test data 
DECLARE @ SQL VARCHAR (MAX) 
The SET @ SQL='the select [Team]' 
SELECT SQL + SQL=@ @ ', Max (case [CostType] the when "' + [CostType] 
+ "' then [Cost] else 0 end) [' + + '[CostType]]' 
The FROM (SELECT DISTINCT 
[CostType] 
The FROM # T 
) a 
SQL=SET @ @ SQL 
+ 'from # T group by [Team]' 
The EXEC (@ SQL) 

CodePudding user response:

Thank you upstairs two great god

CodePudding user response:

In case the when the traditional method,

Use t-sql turn the pivot line of the column can be achieved,

This is very simple