SQL multiple Table federated query

About SQL multiple table joint Query method A lot of, we are today to talk about SQL multiple table query usage and instance code, generally use the SELECT * FROM (SELECT) union to operate.

Let's take a look at the example

Three tables

Charge table

ID Flowid totall
1 K1 3000

Detailed table
ID flowid pid num
1 K1 1 2000
2 K1 2 1000

Payment Form
ID pname
1 cash
2 Checks
3 Card

CREATE TABLE Pay (ID int,flowid varchar (), Totall int)
INSERT into pay Select 1, ' K1 ', 3000

CREATE TABLE detail (ID int,flowid varchar (), PID int,num int)
INSERT into detail Select 1, ' K1 ', 1,2000
INSERT INTO detail select 2, ' K1 ', 2,1000

CREATE TABLE mode (ID int,pname varchar (10))
Insert into mode Select 1, ' Cash '
Insert into mode select 2, ' Check '
Insert into mode Select 3, ' swipe '

DECLARE @sql varchar (8000)
Set @sql = '

Select @sql = @sql + ', [' +pname+ ']=sum (case b.pid when ' +rtrim (ID) + ' then num else 0 ') '
From mode

Set @sql = ' Select A.id,a.flowid,a.totall ' + @sql + ' pay A,detail b where A.flowid=b.flowid GROUP by A.id,a.flowid, A.totall '
EXEC (@sql)

Results：

ID flowid totall Cash Cheque Card
1 K1 3000 2000 1000 0