查找每个位置的案例数，并根据接受贷款的原因对计数进行分类

zd287kbt 于 2021-05-29 发布在 Hadoop

关注(0)|答案(1)|浏览(301)

输入

+---------+--------------------+-----------+
|   id    |       reason       | location  |
+---------+--------------------+-----------+
| 1077501 | credit_card        | AZ        |
| 1077430 | car                | GA        |
| 1077175 | small_business     | IL        |
| 1076863 | other              | CA        |
| 1075358 | other              | OR        |
| 1075269 | wedding            | AZ        |
| 1069639 | debt_consolidation | NC        |
| 1072053 | car                | CA        |
| 1071795 | small_busines      | CA        |
+---------+--------------------+-----------+

输出应为

+----------+-------------+-----+----------------+----------+---------+-------+
| location | credit_card | car | small_business | other... | wedding | total |
+----------+-------------+-----+----------------+----------+---------+-------+
| AZ       |           1 |   0 |              0 |        0 |       1 |     2 |
| CA       |           0 |   1 |              1 |        0 |       0 |     2 |
+----------+-------------+-----+----------------+----------+---------+-------+

输出应为上述格式，因为每个位置都作为一列。

sql hadoop hiveql

来源：https://stackoverflow.com/questions/41002014/find-the-number-of-cases-per-location-and-categorize-the-count-with-respect-to-r

1条答案

按热度按时间

m0rkklqb1#

对动态列使用透视

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
   + QUOTENAME(reason)
FROM (SELECT DISTINCT reason FROM #T) AS Courses

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT location, ' + @ColumnName +',Total = ' + REPLACE(@ColumnName,',[', ' + [') +'
FROM #T
PIVOT(count(Id) 
      FOR reason IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

赞(0）回复(0）举报 2021-05-29

我来回答

查找每个位置的案例数，并根据接受贷款的原因对计数进行分类

1条答案

相关问题

热门标签

最新问答