表PIVOT INEER在SQL中加入 [英] TABLE PIVOT INEER JOIN IN SQL

问题描述

嗨朋友



<罢工>紧急



我的问题是......

我有两张桌子

第一张表是na P is />

 ==== =========================== 
 id PARENTID ITEM 
 1 NULL员工人数
 2 1 UNEMPLOYPERSON 
 3 1 MALEUNEMPLOY 
 4 1 FEMALEUNEMPLOY 
 5 NULL员工
 6 1 UNEMPLOYPERSON 
 7 1 MALEUNEMPLOY 
 8 1 FEMALEUNEMPLOY 
 
 
 ================================ 

==================================



第二张表是PV

 ============================= ======== 
 id PID VALUE 
 1 1 20 
 2 2 45 
 3 3 5 
 4 4 12 
 === ============================== 

其中PID是P表ID



和最后一张桌子是TP

 ============================= ========= 
 ID PID NAME PrentID 
 1 1 COUNTRY null 
 2 1 STATE 1 
 3 1 CITY 2 
 4 2 COUNTRY null 
 5 2 STATE 1 
 6 2 CITY 2 
 =============================== === 

其中pid是P表ID



如何显示这个...... br $>

 IN CITY 
 TOTALPERSON ENEMPLOYPERSON雇员MALEUNEMPLOY 
 82 45 20 5 
 
 
 IN STATE 
 TOTALPERSON ENEMPLOYPERSON雇员MALEUNEMPLOY 
 82 45 20 5 

这不是静态的d ynamic取决于所选择的区域...国家，州，城市

解决方案

   a  as  
（
 选择 ' 员工总数  as 项目，sum（pv.value） as  [value] 
 来自 p 
 内部 加入 pv   p.id = pv.pid 
 
  union  全部 
 
 选择 p.item，pv.value 
 来自 p 
 内部 加入 pv   p.id = pv.pid 
）
  SELECT  [员工总数]，[雇员]，[失业者]，[MALEUNEMPLOY] 
  FROM  
a 
 PIVOT 
（Sum（[value]） FOR  [item] 
  IN （[员工总数]，[员工]，[失业者]，[MALEUNEMPLOY]）
） AS  b 

快乐编码！

：）

尝试动态数据透视：

  DECLARE   @cols   NVARCHAR （ 300 ）
  DECLARE   @ dt   NVARCHAR （ 2000 ）
  DECLARE   @ pt   NVARCHAR （MAX）
 
  SET   @cols  = STUFF（（ SELECT   DISTINCT  ' < span class =code-string>]，[' + [ITEM] 
  FROM  P 
  ORDER   BY  '  ]，[' + [ITEM] 
  FOR  XML PATH（' < span class =code-string>'））， 1 ， 2 ，< span class =code-string>' '）+ ' ]' 
 
  SET   @ dt  = N '  SELECT TP。[NAME]，PV。[VALUE]，P.ITEM' + 
 ' 来自P INNER JOIN TP ON P.ID = TP.PID' + 
 '  INNER JOIN PV ON P.ID = PV.PID' 
 
  SET   @ pt  = '  SELECT [NAME]，' +  @cols  + ' ' + 
 '  FROM（' +  @ dt  + ' ）AS DT' + 
 '  PIVOT（SUM（[VALUE]）FOR [ITEM] IN（' +  @cols  + ' ））A S PT' 
 
  EXEC （ @ pt ）

Hi Friends

It''s urgent

My problem is...
I have two table
First table naem is P

===============================
id	PARENTID	ITEM                 
1	NULL	     EMPLOYE  PERSON         
2	1	     UNEMPLOYPERSON          
3	1	     MALEUNEMPLOY            
4       1 	     FEMALEUNEMPLOY          
5	NULL	     EMPLOYE  PERSON         
6	1	     UNEMPLOYPERSON          
7	1	     MALEUNEMPLOY            
8       1 	     FEMALEUNEMPLOY          


================================

==================================

Second table is PV

=====================================
id	PID	VALUE
1	1	20
2	2	45
3	3	5
4	4	12
=================================

where PID is P Table ID

And the last table is TP

======================================
ID    PID           NAME           PrentID
1     1             COUNTRY         null
2     1             STATE            1
3     1             CITY             2
4     2             COUNTRY          null
5     2             STATE            1
6     2             CITY             2
==================================

where pid is P Table ID

How can I display this like ...

IN CITY
TOTALPERSON     ENEMPLOYPERSON       EMPLOYPERSON    MALEUNEMPLOY     
82                45                      20                 5        


IN STATE
TOTALPERSON     ENEMPLOYPERSON       EMPLOYPERSON    MALEUNEMPLOY     
82                45                      20                 5    

This is not static it is dynamic depending on area selected like ...COUNTRY,STATE,CITY
[Edit - CHill60 SHOUTING removed]

解决方案

with a as
(
select 'Total Employees' as item, sum(pv.value) as [value]
from p
inner join pv on p.id=pv.pid

union all
 
select p.item,pv.value
from p
inner join pv on p.id=pv.pid
)
SELECT [Total Employees],[EMPLOYE  PERSON],[UNEMPLOYPERSON],[MALEUNEMPLOY]
FROM
a
PIVOT
(Sum([value]) FOR [item]
    IN ( [Total Employees],[EMPLOYE  PERSON],[UNEMPLOYPERSON],[MALEUNEMPLOY])
) AS b

Happy Coding!
:)

Try dynamic pivot:

DECLARE @cols NVARCHAR(300)
DECLARE @dt NVARCHAR(2000)
DECLARE @pt NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + [ITEM]
			FROM P
			ORDER BY '],[' + [ITEM]
		FOR XML PATH('')),1,2,'') + ']'

SET @dt = N'SELECT TP.[NAME], PV.[VALUE], P.ITEM ' + 
	'FROM P INNER JOIN TP ON P.ID = TP.PID ' +
		'INNER JOIN PV ON P.ID = PV.PID '

SET @pt = 'SELECT [NAME], ' + @cols + ' ' +
         'FROM (' + @dt + ') AS DT ' +
         'PIVOT(SUM([VALUE]) FOR [ITEM] IN(' + @cols + ')) AS PT'

EXEC (@pt)

这篇关于表PIVOT INEER在SQL中加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！