none
split row in 3 columns

    Question

  • How can i split the records in 3 colums

    declare @sql varchar(400)

    set @sql='powershell.exe  "Get-WmiObject -namespace root\MSCluster -class MSCluster_Resource | Where-object  {$_.type -eq ''Physical Disk''} | SELECT Name,Type,State"'


     CREATE TABLE #output
     (line varchar(255))
     --inserting disk name, total space and free space value in to temporary table
     insert #output
     EXEC xp_cmdshell @sql

    select * from #output


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

    Friday, June 13, 2014 5:01 AM

Answers

  • Could not test for all data, but you can test the below:

    declare @output Table(line varchar(255))
    Insert into @output Values
    ('MVM_ARCHIVE_QA_SQL_01      Physical Disk                                      2'),
    ('MVM_QUORUM_QA_SQL_01       Physical Disk                                      2')
    
    ;With cte as
    (
     SELECT        Replace(replace(
                      replace(
                         replace(
                            LTrim(RTrim(line)), 
                         '  ',' |'),                    
                      '| ',''),                         
                   '|',''),' ','.') AS removed From @output
    )Select 
    		PARSENAME(removed,4) as 'Col1',
    		PARSENAME(removed,3)+' '+PARSENAME(removed,2) 'Col2',
    		PARSENAME(removed,1) 'Col3'
     From cte
    

    Friday, June 13, 2014 6:45 AM

All replies

  • You may refer the below gallery: A ready-made script

    http://gallery.technet.microsoft.com/Drive-Capacity-InfoDetails-f2b8fb60

    Friday, June 13, 2014 5:38 AM
  • Hi Latheesh,

    I hope you should have read the question. I am quering MSCluster_Resource  class to get the cluster drive name,type and status,the output is merged so need to split.

    Regards

    Sufian


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.


    Friday, June 13, 2014 6:22 AM
  • Ok, I have some issues to execute your query in my environment.

    Could you please share your output data, so that we would be able to help you better.

    Friday, June 13, 2014 6:26 AM
  • MVM_ARCHIVE_QA_SQL_01      Physical Disk                                      2
    MVM_ARCHIVE_UA_SQL_01      Physical Disk                                      2
    MVM_BACKUP_QA_SQL_01       Physical Disk                                      2
    MVM_DATA_QA_SQL_01         Physical Disk                                      2
    MVM_DATA_UA_SQL_01         Physical Disk                                      2
    MVM_LOG_QA_SQL_01          Physical Disk                                      2
    MVM_LOG_UA_SQL_01          Physical Disk                                      2
    MVM_QUORUM_QA_SQL_01       Physical Disk                                      2
    MVM_TEMP_QA_SQL_01         Physical Disk                                      2
    MVM_TEMP_UA_SQL_01         Physical Disk                                      2
    MVM_UTILITY_QA_SQL_01      Physical Disk                                      2

    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

    Friday, June 13, 2014 6:29 AM
  • Could not test for all data, but you can test the below:

    declare @output Table(line varchar(255))
    Insert into @output Values
    ('MVM_ARCHIVE_QA_SQL_01      Physical Disk                                      2'),
    ('MVM_QUORUM_QA_SQL_01       Physical Disk                                      2')
    
    ;With cte as
    (
     SELECT        Replace(replace(
                      replace(
                         replace(
                            LTrim(RTrim(line)), 
                         '  ',' |'),                    
                      '| ',''),                         
                   '|',''),' ','.') AS removed From @output
    )Select 
    		PARSENAME(removed,4) as 'Col1',
    		PARSENAME(removed,3)+' '+PARSENAME(removed,2) 'Col2',
    		PARSENAME(removed,1) 'Col3'
     From cte
    

    Friday, June 13, 2014 6:45 AM
  • thanks Latheesh.


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

    Friday, June 13, 2014 6:48 AM