arşiv

Cuma, 19 Oca 2018 için arşiv

Vb script – MapNetworkDrive

Cuma, 19 Oca 2018 yorum yok
Option Explicit

Dim objNetwork, objFSO, objShell

Dim strDriveLetter, bForce, bUpdateProfile, strNewName
strDriveLetter = "Z:"
bForce = "True"
bUpdateProfile = "True"
strNewName = "\\bıdıbıdı\folder"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objNetwork = CreateObject("Wscript.Network")

If (objFSO.DriveExists(strDriveLetter) = True) Then
objNetwork.RemoveNetworkDrive strDriveLetter, True, True
End If
Set objNetwork = CreateObject("WScript.Network")
objNetwork.MapNetworkDrive strDriveLetter, strNewName

Set objShell = CreateObject("Shell.Application")
objShell.NameSpace(strDriveLetter).Self.Name = strNewName

' Wscript.Echo "Check : "& strDriveLetter & " for " & strNewName
WScript.Quit

 

Categories: VBScript Tags:

Sql XML PATH

Cuma, 19 Oca 2018 yorum yok

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

Select Main.SubjectID,
       Left(Main.Students,Len(Main.Students)-1) As "Students"
From
    (
        Select distinct ST2.SubjectID, 
            (
                Select ST1.StudentName + ',' AS [text()]
                From dbo.Students ST1
                Where ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                For XML PATH ('')
            ) [Students]
        From dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a subquery:

 

Select distinct ST2.SubjectID, 
    substring(
        (
            Select ','+ST1.StudentName  AS [text()]
            From dbo.Students ST1
            Where ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            For XML PATH ('')
        ), 2, 1000) [Students]
From dbo.Students ST2

https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

Categories: SQL Hakkında Tags: ,