如何在t-sql中将一列的结果分隔为多列？

mklgxw1f 于 2021-07-24 发布在 Java

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

我试图获取有关磁盘驱动器、总空间和可用空间（共3列）的信息。我在网上发现了一个与我的需求相似的查询，但该查询需要一些修改。我做了但被卡住了。我找不到中柱。你能帮我吗？

create table #mainDOScmd (ExecCommand NVARCHAR(500))
insert into #mainDOScmd
EXEC master..xp_cmdshell 'wmic LOGICALDISK where "DriveType=3" get DeviceID, FreeSpace, Size';
select * from #mainDOScmd
CREATE TABLE #TEMP( 
[DriveLetter] VARCHAR(100),
[TotalDriveSpace(GB)] NVARCHAR(500), 
[FreeSpaceOnDrive(GB)] NVARCHAR(500)
)
INSERT INTO #TEMP
SELECT 
LEFT(ExecCommand, PATINDEX('%  %',ExecCommand)),
LTRIM(RIGHT(ExecCommand,LEN(ExecCommand)-PATINDEX('%  %',ExecCommand))), --problematic part
LTRIM(RIGHT(LTRIM(RIGHT(ExecCommand,LEN(ExecCommand)-PATINDEX('%  %',(ExecCommand)))), LEN(LTRIM(RIGHT(ExecCommand,LEN(ExecCommand)-PATINDEX('%  %',(ExecCommand))))) - PATINDEX('%  %',LTRIM(RIGHT(ExecCommand,LEN(ExecCommand)-PATINDEX('%  %',REVERSE(ExecCommand))))))) 
FROM #mainDOScmd
WHERE ExecCommand IS NOT NULL AND RTRIM(LEFT(ExecCommand, PATINDEX('%  %',ExecCommand))) <> 'DeviceID'
select * from #TEMP
DROP TABLE #TEMP
DROP TABLE #mainDOScmd
GO

sql String tsql xp-cmdshell

来源：https://stackoverflow.com/questions/62576510/how-to-separate-a-result-of-one-column-into-multiple-columns-in-t-sql

1条答案

按热度按时间

093gszye1#

这里有一个快速的解决方案。只需将问题部分的最后一列值替换为nothing

CREATE TABLE #mainDOScmd (ExecCommand NVARCHAR(500))

INSERT INTO #mainDOScmd
EXEC master..xp_cmdshell 'wmic LOGICALDISK where "DriveType=3" get DeviceID, FreeSpace, Size';

SELECT *
FROM #mainDOScmd

CREATE TABLE #TEMP (
    [DriveLetter] VARCHAR(100)
    ,[TotalDriveSpace(GB)] NVARCHAR(500)
    ,[FreeSpaceOnDrive(GB)] NVARCHAR(500)
    )

INSERT INTO #TEMP
SELECT LEFT(ExecCommand, PATINDEX('%  %', ExecCommand))
    ,replace(LTRIM(RIGHT(ExecCommand, LEN(ExecCommand) - PATINDEX('%  %', ExecCommand))), ' ' + LTRIM(RIGHT(LTRIM(RIGHT(ExecCommand, LEN(ExecCommand) - PATINDEX('%  %', (ExecCommand)))), LEN(LTRIM(RIGHT(ExecCommand, LEN(ExecCommand) - PATINDEX('%  %', (ExecCommand))))) - PATINDEX('%  %', LTRIM(RIGHT(ExecCommand, LEN(ExecCommand) - PATINDEX('%  %', REVERSE(ExecCommand))))))), '')
    ,LTRIM(RIGHT(LTRIM(RIGHT(ExecCommand, LEN(ExecCommand) - PATINDEX('%  %', (ExecCommand)))), LEN(LTRIM(RIGHT(ExecCommand, LEN(ExecCommand) - PATINDEX('%  %', (ExecCommand))))) - PATINDEX('%  %', LTRIM(RIGHT(ExecCommand, LEN(ExecCommand) - PATINDEX('%  %', REVERSE(ExecCommand)))))))
FROM #mainDOScmd
WHERE ExecCommand IS NOT NULL
    AND RTRIM(LEFT(ExecCommand, PATINDEX('%  %', ExecCommand))) <> 'DeviceID'

SELECT *
FROM #TEMP

DROP TABLE #TEMP

DROP TABLE #mainDOScmd

赞(0）回复(0）举报 2021-07-24

我来回答

如何在t-sql中将一列的结果分隔为多列？

1条答案

相关问题

热门标签

最新问答