sql - Expand Col values based on names/email SQL
问题描述
I want to explode my table based on their 'KEY's and assort their names and emails separately into individual cols.
In Python such scenarios are dealt easily(like we use explode in pandas). since I've just started with SQL I'm finding it difficult to get my feet wet. My googling didn't help me even next to were i want, hence reaching out to the community as a my last resort for some guidance. In essence i'm trying to expand my table with my KEY as the index and the names and emails exploded across multiple cols.
my table:
KEY | FIRST | MIDDLE | LAST | FLAG_GENDER | FLAG_DESCENT | |
---|---|---|---|---|---|---|
1 | ROBIN | A | SIMPSON | ROBIN@PROTON.COM | M | - |
1 | NICOLE | P | SIMPOSON | NIC@YAHOO.COM | F | NA |
1 | SANDY | LAYNE | F | NA | ||
1 | BRUCE | NILLS | BRUCENILLS@ | M | NA | |
1 | ERIC | WOOTEN | ERICW@YAHOO.COM | M | NA | |
5 | JUDY | THAMES | JUDYTHAMES@YAHOO.COM | F | NA | |
5 | JUDY | THAMES | JUDY@GMAIL.COM | F | NA | |
A290 | RENN | J | JOHNSON | RENNY@COMCAST.COM | M | C |
K890 | JAMES | RODRIGUES | NA | M | L | |
189 | BECKIE | KATE | MOORS | BECKIE@GMAIL.COM | F | - |
189 | BECKIE | MOORSB@YAHOO.COM | F | - | ||
189 | MOORS | BMOORS@GMAIL.COM | F | - | ||
189 | BECKIE | KATE | MOORS | BECKIE@GMAIL.COM | F | - |
output table:
KEY | FULL NAME_1 | FULL NAME_2 | FULL NAME_3 | FULL NAME_4 | FULL NAME_5 | EMAIL_1 | EMAIL_2 | EMAIL_3 | EMAIL_4 | EMAIL_5 |
---|---|---|---|---|---|---|---|---|---|---|
1 | ROBIN A SIMPSON | NICOLE P SIMPOSON | SANDY LAYNE | BRUCE NILLS | ERIC WOOTEN | ROBIN@PROTON.COM | NIC@YAHOO.COM | NA | BRUCENILLS@ | ERICW@YAHOO.COM |
5 | JUDY THAMES | JUDY THAMES | JUDYTHAMES@YAHOO.COM | JUDY@GMAIL.COM | ||||||
A290 | RENN J JOHNSON | RENNY@COMCAST.COM | ||||||||
K890 | JAMES RODRIGUES | NA | ||||||||
189 | BECKIE KATE MOORS | BECKIE MOORS | MOORS | BECKIE KATE MOORS | BECKIE@GMAIL.COM | MOORSB@YAHOO.COM | BMOORS@GMAIL.COM | BECKIE@GMAIL.COM |
解决方案
正如我在评论中指出的那样,我强烈建议这是Group by column 和 multiple Rows into One Row 多列的副本,但是,以演示如何为 2 列而不是 1 列执行此操作:
WITH RNs AS(
SELECT [KEY], --KEY is a reserved keyword and should not be used for object names.
STUFF(CONCAT(' ' + [FIRST], ' ' + MIDDLE, ' ' + [LAST]),1,1,'') AS FullName, --FIRST and LAST are reserved ODBC keywords and should be avoided for object names.
--If you're on 2017+ you can use CONCAT_WS:
--CONCAT_WS(' ',[FIRST], MIDDLE, [LAST]) AS FullName,
Email,
ROW_NUMBER() OVER (PARTITION BY [KEY] ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column --KEY is a reserved keyword and should not be used for object names.
FROM dbo.YourTable)
SELECT [KEY], --KEY is a reserved keyword and should not be used for object names.
MAX(CASE RN WHEN 1 THEN FullName END) AS FullName1,
MAX(CASE RN WHEN 2 THEN FullName END) AS FullName2,
...
MAX(CASE RN WHEN 1 THEN Email END) AS Email1,
MAX(CASE RN WHEN 2 THEN Email END) AS Email2,
...
FROM RNs R
GROUP BY [KEY]; --KEY is a reserved keyword and should not be used for object names.
如果您需要一个动态的解决方案,那么链接的重复候选者中也会展示这一点;原理也是一样的。
推荐阅读
- iis - 为本地主机问题创建自签名证书?
- sql - 如何找到 TableA 中的行元素与 TableB 不同的两个表的行
- react-native - 深度链接 - 未调用 getInitialURL 和订阅
- python - 在不属于任何标签的文本上训练 Spacy TextCategorizer
- typescript - 如何跨对象属性正确推断类型?
- python - 将 Keras Dense 连接到 Conv2D 输出
- python - Python中将字符串拆分为n个子字符串的排列
- error-handling - 将 varchar 值“4A”转换为数据类型 int 时转换失败
- javascript - 如何从类型定义中模拟声明的类?
- html - 将文本内容包装在剪辑路径多边形(三角形)形状内,并将图像剪辑在另一半