我有2个表,一个表包含需要计数的列名列表,另一个表包含要计数的数据
Table A
ProgramId ColumnName
1 Country
1 Gender
2 AgeRange
2 Region
Table B
ProgramId Country Gender AgeRange Region
1 USA M 18-25 Midwest
1 USA F <18 Northeast
1 MEX M <18 South
2 USA M 18-25 Midwest
2 USA M 26-35 Midwest
给定一个特定的ProgramId,我需要一个表A的每一列的列表,以及每个值和表B的计数
需要示例输出(参数:@ProgramId = 1)
ColumnName ColumnValue ValueCount
Country USA 2
Country MEX 1
Gender M 2
Gender F 1
从我能够找到的谷歌搜索中,我将需要某种动态声明。对我来说,这很好,因为唯一有权访问表A的人将是内部开发人员。
MS SQL Server 2008架构设置:
CREATE TABLE TableA (ProgramId INT, ColumnName VARCHAR(20))
INSERT INTO TableA VALUES
(1 ,'Country'),
(1 ,'Gender'),
(2 ,'AgeRange'),
(2 ,'Region')
CREATE TABLE TableB (ProgramId VARCHAR(20), Country VARCHAR(20),
Gender VARCHAR(20), AgeRange VARCHAR(20), Region VARCHAR(20))
INSERT INTO TableB VALUES
('1' ,'USA' ,'M' ,'18-25' ,'Midwest'),
('1' ,'USA' ,'F' ,'<18' ,'Northeast'),
('1' ,'MEX' ,'M' ,'<18' ,'South'),
('2' ,'USA' ,'M' ,'18-25' ,'Midwest'),
('2' ,'USA' ,'M' ,'26-35' ,'Midwest')
查询1:
;With x AS
(
Select *
From TableB
UNPIVOT(ColumnValue FOR ColumnName IN (Gender, Country))up
)
Select x.ColumnName
,x.ColumnValue
,COUNT(x.ColumnValue) ValueCount
From x
inner join TableA a ON x.ColumnName = a.ColumnName
WHERE x.ProgramId = 1 --<-- or maybe the parameter @ProgramId
GROUP BY x.ColumnName,x.ColumnValue
结果:
| ColumnName | ColumnValue | ValueCount |
|------------|-------------|------------|
| Country | MEX | 1 |
| Country | USA | 2 |
| Gender | F | 1 |
| Gender | M | 2 |
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句